Replace multiple values in Power Query in one step

In todays video I will show you how to conditional replace values in one step without adding new columns in Power Query, Enjoy!!
Chapters:
00:00 Intro & Problem description
00:36 Replace values the normal way
01:37 The solution
Done!
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE

[@dapperlink]

This has worked beautifully for me until I tried to replace nulls, then it just stared back at me. I finally tried adding a pre step by replacing the nulls with something like “empty’, then used it in my conditional replace statement and “OMG” it worked! Maybe someone’s already mentioned this in the comments and if so, I’m just helping get the word out. This is one of my favorite hacks. Thanks again Ruth!

[@CurbalEN]

And thanks for sharing your hack!! Surely will be helpful to others :)

[@melinalouzada6840]

I had the same problem, although replace values null to something else, on its own, with no if statements in replace value formula work fine?

[@PedroCabraldaCamara]

such a cool tip Ruth. I've been "waiting" for this for so long....."change this column if that column...". Awesome!

[@CurbalEN]

We have it now !! /Ruth

[@RH-ko6pz]

This has cleaned up so many of my queries! no longer messing around with creating unneeded columns, only to remove them later! :)

[@CurbalEN]

Exactly :) /Ruth

[@trustnoone6065]

I'm a data analyst. Really it helped me with real time project. Thank u so much

[@roytaylor4008]

Brilliant. Thank you. I put in an 'else if' into my formula as well so I could do multiple replaces in one go.

[@evcmiranda]

The BEST, DIRECT, SOLVABLE explanation for this case EVER!!!!! I do not know why, but, in my example, I had to change the parameter "Replacer.ReplaceText" to "Replacer.ReplaceValue". I will try to understand later.

[@CurbalEN]

🥳🥳🥳

[@buhogris]

Thank you, it wasn't working for me, this helped understand it!

[@nishantkumar9570]

Awesome, i was searching for it for days.... And you made it so simple. Thank you very much.

[@justapasserby69420]

Thanks for this! I was able to replace multiple items in one column thanks to this tip. P.S. You can use the OR, e.g. each if [Type] = "System" or [Type] =""

[@jegramat]

Finally I found what I was looking for. Now I am wondering if there is a way to do multiple columns replacement at once. That would be very helpful.

[@munafdamani6233]

Your videos on power query are really helpful to me in my data analysis. Thanks you for sharing knowledge to us. 🙏

[@CurbalEN]

My pleasure and thanks for the feedback!

[@tonyhartigan4668]

Wow, this just replaced about 25 Power Query steps with one line!!!! You are Numero Uno!

[@CurbalEN]

Gracias!!

[@brendaz]

OMG! I've looking how to do this for days. You saved my life. Thank you! 💗

[@CurbalEN]

💛

[@wayneedmondson1065]

Awesome! Thanks for this great tip Ruth. Thumbs up!!

[@teenoou3132]

Hi, great series, just started following! A quick question you might have answered before, how do I dynamically filter or subset a table based on a slicer value with Dax?

[@thyster11]

Thank you thank you Ruth - Curbal. You are life saver.

[@CurbalEN]

The pleasure is all mine!

[@jsonify]

This was sooooo awesome and helpful. I have been trying so hard to get this to work prior to you trick and I am so thankful for you help! Cheers!

[@CurbalEN]

Thrilled to share and help :)

[@wasio44]

Hi @Curbal, many thanks! I would like to do such adding prefix, but not based on text value ("Sys_" in your example), but based on a third column. do you have an idea to solved that? many thanks

[@abhilashg287]

Hi, I have user table with 2 columns, 'User ID' and 'User Name'. 'User Name' is null for some User ID's. I'm trying to add names to 'User Name' column based on user id's in power query. But for null values the name is not adding. Below is the query I'm using:- = Table.ReplaceValue(#"Replaced Value2",each [User Name],each if [User ID]="abc" then "Name" else [User Name],Replacer.ReplaceText,{"User Name"}) Please let me know what is the issue here. Because If i try replacing null with space and again try above formula if works absolutely fine. But not when cell value is 'null'

[@donovannewton9507]

This is great. I’m trying to do something like this. In the the column Round 1, I have 1 v2, 3 v 4.. etc. I want to add the list of names to correspond to the numbers and replace them. I thought about pivoting the List of names to match the data, but I think there is a cleaner way to do it. I know I could split the columns, change the values and then merge the columns back. But that seems like a lot of work

[@Ryan-zz6hh]

Great video, solves a very frequent challenge in PQ quickly and easily!

[@CurbalEN]

Music to my ears!

[@adambaker2463]

hii, thanks again, it's a useful video however i have a question, is there a way to replace ALL values in a matrix with a 1? I can obviously use a conditional formatting for each column to say if it does not equal null then 1 but i have many many columns (all differently named) and so I was wondering if theres a way to bulk replace values with 1?

[@TV-sr6pq]

항상 열심히 구독하고 있습니다 좋은 자료 고맙습니다. 행복하세요^^

[@CurbalEN]

이제 둘 다 행복하길 바라

[@EricaDyson]

You saved me once again! I actually needed ReplaceValue but the method is, of course, identical. Mega thanks.

[@rami765]

how did you do it with replace values? only values not the whole word?

[@EricaDyson]

@@rami765 Not quite sure what you mean... happy to help if I can. Can you explain a bit more what you're after?

[@locomotech6302]

Thank you very much, it took me back to my beginnings with power query. The root of things: code and functions This is more than wonderful! But what about replacing these values through "another mapping table" variable , external or inside the same excel file, adjustable at a later time with fixed code? Thanks in advance!

[@ArthurFreitag]

this was literally all i was looking for today. Thank you very much

[@CurbalEN]

My pleasure!

[@joshblackman2380]

Once again, clutch code. This is just great, great, great. Perfect. Thank you!

[@CurbalEN]

Clutch code?? Never heard that before, but based on the rest of your words I take it as a positive word? 🤣

[@asjones987]

Love this video... it I has saved me so much time. Then today I ran into a case where I wanted to combine two steps and do a conditional Multiply by -1 without having to add an extra column. At first I thought i figured it out doing a table.transform multiply by -1 from the gui then adding an If statement in it but that just does not seem to work. Is it possible?

[@alizaher5491]

Great and very useful tip which saves me today!

[@donjoncho5878]

Hola Ruth. De que manera puedo hacer una sustitución o remplazo de valores dentro de una columna? Por ejemplo, si quiero reemplazar Á por A, É por E, etc?

[@lastdance903]

Hi, there is one problem I encountered when I tried to replace with decimal values with this method and the the replaced value was rounded up automatically. Without the each if statement embedded, the ReplaceValue works perfectly fine with decimal numbers. Still couldn't figured out why after many attempts.

[@niranjansaikrishnac4394]

Without knowing this trick, have been using if so stupidly. Thanks for the cool trick Ruth. As always thank you!

[@CurbalEN]

Remeber, we all have to start somewhere!! /Ruth

[@Henrik.Vestergaard]

I knew it would come in handy some day - today was the day :-)

[@CurbalEN]

I use that method too :)

[@gabrieldenoury275]

Great, simple and effective!

[@mariaalcala5159]

Thank you very much! Great trick!

[@rodneypearman8643]

You have just saved me hours!!!! I really must get deeper into M.....

[@CurbalEN]

Do it!! You won't regret it!

[@johnlandriau3825]

Great video. Thank you!

[@mylinhvu9359]

This is incredible. Thank you

[@rajvihari4326]

I am always waiting for Monday Wednesday and Dad Friend eagerly.... Good job👍

[@CurbalEN]

Thanks! /Ruth

[@charlescaison4376]

Great. So simple yet so powerful. Thank you.

[@CurbalEN]

One of my favorites:)

[@MrAszpic]

We definitely need more of this PQ tricks

[@CurbalEN]

Hopefully I have some more in my bag to show :) /Ruth

[@sarabashi1269]

You are the best! save my life :D Thank you for making this beautiful videos.

[@CurbalEN]

Thank you for the feedback Sara ❤️

[@dontgetcaughtslippn6075]

This is a game changer in how I can improve performance of my queries!

[@CurbalEN]

Great to hear!

[@klaustrampedach7312]

A really nice trick, I keep returning to. Today I discovered that you can even use a function for the 'Replace with' parameter :)

[@CurbalEN]

Power query is wonderful!

[@bryanleow5024]

This is such a clean implementation! THanks!

[@CurbalEN]

My pleasure!!

[@shashankchandramouli34]

i am doing dashboard for animals dataset, there is a column named threats , in that column the values are sentences , like " The major threat is deforestation ...." ,i want to replace these kind of sentences to one word like " deforestation " based on condition like if the row value contains this word then change it to another word . is it possible to that ?

[@BruceFonseca1]

very cool. helped me a lot. thank you!!!

[@wants77]

Great info. Thanks. How could I do if I need to write the code with several changes?

[@alexlijesen6197]

Thanks for your clear explanation but I would like to go a little deeper. The following: Can you also change value in the cell with a step e.g. search for “->” and change that to “|” with an if function Old current cell value = “explanation ->” New value in cell = "explanation |"

[@JamesFujitsu]

Thanks for this it really helped me

[@user-ms9hs2qg8o]

Thank you, this is amazing. How can I replace different value with null? I have got some numbers and letters in a column and I have to replace with null. Any suggestion?

[@vicentemedel8469]

thanks you so much !!! hi from Chile!

[@CurbalEN]

Hola 👋

[@olaayorinde6865]

Thanks for video! Pls can you do one on replace values for EXACT word match, for example I have this list in a column - Strong, St, Chest, Staite, state, stouts but I only want to change "St" to Street. How can I do this without other words being affected. Thanks

[@omarbn92]

Thanks for the video, i tried this replacing blanks ("") and nullls but it doesnt work. It can not recognize theses values: Replacevalues(table, each[column], each if [column] ="" or [coumn] = null then a else b, replacer...) For other value it works Is there a specific rule in this case? :)

[@selwynvanrooyen9432]

Super cool. What would the code look like to do this step on multiple fields? (So not just 'each [Process]', but something like 'each [Process],[Process1]' etc) ?

[@goodheart2817]

Hello...Thank you for your video. I have a BIG PROBLEM. I want to input A's into several columns on the same row which has number 16. How do i do that please?

[@yungslay]

Is it possible to do something similar to this with replacing duplicates within a column to a number?

[@nainarastogi2096]

Does this work if we have dynamic value .Will this replace will data that comes in future.

[@kasra7777]

Thanks, super helpful

[@joanneosborne2428]

Thanks, Ruth. This is a great tip.

[@CurbalEN]

Really useful in many situations 😃😃 /Ruth

[@joebateman9668]

Great tip, I've been trying to find an M code solution like this for a while to help remove parentheses and to he text between. Will give this ago for sure.

[@CurbalEN]

It makes writing M cleaner, for sure!

[@joseagundis1]

Buen tip, gracias por compartir. Saludos Ruth

[@CurbalEN]

Saludos!!

[@michamachowski1627]

Thank you so much Ruth! Another extremely useful video and another problem at work solved! Liked and subscribed :)

[@CurbalEN]

Welcome!!

[@monalymistry2010]

Can you do this with applying a function to column as well?

Marvelous!. Thank you. And now a challenge. I have a table with n months how heading. They store the amount of product to buy. I have a price column per product. I want to replace the monthly quantity columns with values multiplying each column of (amount month) * price. Without creating additional columns. The quantity and names of the columns is dynamic. What do you think?

[@skulz66]

Great video! thank u

[@lukasjevcik704]

Hi, I have tried the solution but it does not work. Tried with just the square brackets and with the [#"Column Name"] format, yet it does not replace the values. The Column Name does not contain anything other than a space and underscore. Am I missing something? I am thankful for any suggestions. Example: = Table.ReplaceValue(#"Expanded Log",each [Implementation Group], each if [Implementation Group_Log] null then [Implementation Group_Log] else [Implementation Group],Replacer.ReplaceText,{"Implementation Group"})

[@huubske9]

Great vid, thanks

[@sandyhodge491]

Wow, this is a really cool and useful tip, thanks for sharing! :)

[@CurbalEN]

You welcome Sandy!

[@jorgerobertobalardingarcia6397]

Great tip. I´m looking for M code to replace multiple values in a column based on each string in the column. Like this: if [column name] contains string HOSPITAL", replace by HOSP, else if [column name] contains string "ASSOCIATION" replace by ASSOC. I know how to do that line per line, but not in one statement. Thanks in advance.

[@mohamedbahi4979]

Thank you so much

[@faisalbaig9969]

You are Amazing. I was finding this for long time and now have solution. One question, If I have multiple entries need replacement. I used OR into formula but have to write Column reference all the time. Is it possible to write without reference and just multiple entries. I don't know either it is possible.

[@TheCiafm]

Very usefull, Ty!!!

[@duskage99]

that saved me alot of time ty!

[@CurbalEN]

You welcome!

[@Pauloswin]

Will this work if I have multiple numbers In a single cell? E.g A1 contains 1, 30, 66 and I want to replace 1 with 22, 30 with 44 and 66 with 70? The old and new numbers are listed in two separate columns.

[@FredrikDahlberg]

You are absolutely amazing!! 😍

[@CurbalEN]

🥳🥳

[@mamdouhalsalamouny4309]

Thank you great

[@soy34mb]

Definitely, it is really cool that trick! , and the most important, it is very useful one! . Thanks for sharing!.

[@CurbalEN]

M is an amazing language 😍 /Ruth

[@rodneypearman8643]

Thanks again for a great tip - this really highlights why I’ve been wanting to. Real into M - any tips on how best to do this?

[@CurbalEN]

Tips on how to do M? /Ruth

[@HasithaSamarajeewa]

Is there a way to replace values in multiple columns. I am trying to figure out how to put all the columns (as a list?) With the each and each if statements

[@hcedenop]

Ruth! this is awesome ✌ it does bring a lot of possibilities for PQ & M... and Yay: less DAXing 😁

[@CurbalEN]

Team Power Query!!

[@hcedenop]

Curbal Yay! PQ&M 👍🏼

[@MrDhunpagla]

You bring smile & knowledge both together ma'am......very nice & helpful tip 👌😃

[@CurbalEN]

At your service :) /Ruth

[@database_tips_tricks]

always waiting for your next videos such cool tips allways

[@CurbalEN]

Thanks!! 😊😊

[@mittenwald3679]

nice thanks again !!!

[@ymohamed3154]

thanks you so much

[@StevietheMaster]

Is it possible to replace multiple values in more than one column in some similar way?

[@jsx0328]

What does it mean the word source? how is that different than passing in the column that you are trying to replace values from?

[@allonewordlowercase]

Ruth you are amazing!

[@CurbalEN]

You too!

[@CarlosEliezerHernandezPintor]

Awesome trick! Very appreciated

[@CurbalEN]

One of my favorites!

[@rextkm]

EXCELENT!!!

[@rendyshadows]

always being great problem solver

[@philipedesouzasantos1201]

Thank you miss

[@o_felipecarvalho]

It Is just amazing how every problem that I had in Power Query I found the solution on this Channel. I have two accounts and you deserve 2 subscriptions!

[@CurbalEN]

Thanks! :)

[@o_felipecarvalho]

I did not get any error but for some reason, that did not work. I know I can do this in more steps: 1) create a condition new Column. IF the value in Column2 is Null, then get the Value from Column5, Otherwise keep Column2 Value. 2) Delete Column2 3) Rename the new Column to Column2. but I was hoping to apply what you showed ... but nothing happens! = Table.ReplaceValue(#"Duplicatas Removidas",each [Column2], each if [Column2] = null then [Column5] else [Column2],Replacer.ReplaceText,{"Column2"}) What Am I Missing?

[@CurbalEN]

Impossible to say without seeing the data, would you posting in the power bi community ? Make sure you share some sample data.

[@o_felipecarvalho]

@@CurbalEN i Try to post but had a problem with the login... I keep searching and had no idea how I came up to this mix , but IT WORKED! : = Table.ReplaceValue(Capitalize,null,each [Column5],Replacer.ReplaceValue,{"Column2", "Column5"}) On the Colum2 , every time it has a null value it replaces with the Value from the Column5 . Sometimes the Column5 has null values as well and that is ok. All the Columns on the table have been previously formatd as Text, even do it contains Text and Number. Hope this help anyone. I don't have any idea the WHY it worked.

[@venkatareddykummitha5863]

I want to change the year ( future year) in the entire date column. Is that possible

[@abhijeetghosh27]

Super cool 👌👌

[@carlosraziel7141]

Querida Ruth, como puedo remover el texto que se encuentra en una columna que esta en tabla anidada en una columna? No encuentro la forma 😭🤧 por favor.

[@Ania-xn6vo]

Will this trick only work if I have two columns? I have multiple values is one column that need replacing. Is there a trick for this scenario?

[@muhammadmuneebkhanafridi154]

Thank you for thiss!!

[@CurbalEN]

Yay!

[@davidferrick]

Why have I not used this before, I got really tired of creating endless helper columns for multi-conditional situations. Thank you!

[@CurbalEN]

Cleaner code, for sure!

[@DIGITAL_COOKING]

great one ! , 👍👍👍👍👍

[@CurbalEN]

Yey!!

[@Kats072307]

my colleagues chose to use a very high value, instead of zero, just to show that there is connection - communication, and now I want to replace in a matrix visual from all these 40-45 columns in a matrix, wherever value > 990, to zero Is this possibe ? I simply want to avoid creating other 40-45 customized columns for this case