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!


SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

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:


Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲


************





************


QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#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!

[PedroCabraldaCamara]

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

[RH-kopz]

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

[wasio]

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

[teenoou]

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?

[jsonify]

This was 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!

[nishantkumar]

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

[evcmiranda]

The BEST, DIRECT, SOLVABLE explanation for this case

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.

[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.

[justapasserby]

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] ="<Any other item>"

[TV-srpq]

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

[bryanleow]

This is such a clean implementation! THanks!

[roytaylor]

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

[thyster]

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

[donovannewton]

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

[asjones]

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?

[Ryan-zzhh]

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

[charlescaison]

Great. So simple yet so powerful. Thank you.

[adambaker]

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?

[sandyhodge]

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