Replace multiple values in Power Query in one step

Рет қаралды 110,698

Күн бұрын

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

Пікірлер: 202

@frazicl Жыл бұрын

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 4 жыл бұрын

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

@CurbalEN 4 жыл бұрын

We have it now !! /Ruth

@evcmiranda 3 жыл бұрын

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 3 жыл бұрын

🥳🥳🥳

@buhogris 2 жыл бұрын

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

@RH-ko6pz 3 жыл бұрын

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

@CurbalEN 3 жыл бұрын

Exactly :) /Ruth

@roytaylor4008 Жыл бұрын

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

@thyster11 4 жыл бұрын

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

@CurbalEN 4 жыл бұрын

The pleasure is all mine!

@nishantkumar9570 3 жыл бұрын

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

@TV-sr6pq 3 жыл бұрын

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

@CurbalEN 3 жыл бұрын

이제 둘 다 행복하길 바라

@wayneedmondson1065 2 жыл бұрын

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

@gabrieldenoury275 2 жыл бұрын

Great, simple and effective!

@mylinhvu9359 3 жыл бұрын

This is incredible. Thank you

@charlescaison4376 3 жыл бұрын

Great. So simple yet so powerful. Thank you.

@CurbalEN 3 жыл бұрын

One of my favorites:)

@johnlandriau3825 2 жыл бұрын

Great video. Thank you!

@jegramat 2 жыл бұрын

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 2 жыл бұрын

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

@CurbalEN 2 жыл бұрын

My pleasure and thanks for the feedback!

@trustnoone6065 4 ай бұрын

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

@bryanleow5024 3 жыл бұрын

This is such a clean implementation! THanks!

@CurbalEN 3 жыл бұрын

My pleasure!!

@jsonify 3 жыл бұрын

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 3 жыл бұрын

Thrilled to share and help :)

@mariaalcala5159 2 жыл бұрын

Thank you very much! Great trick!

@alizaher5491 3 жыл бұрын

Great and very useful tip which saves me today!

@brendaz 2 жыл бұрын

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

@CurbalEN 2 жыл бұрын

💛

@justapasserby69420 2 жыл бұрын

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] =""

@Ryan-zz6hh 3 жыл бұрын

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

@CurbalEN 3 жыл бұрын

Music to my ears!

@joshblackman2380 3 жыл бұрын

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

@CurbalEN 3 жыл бұрын

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

@kasra7777 3 жыл бұрын

Thanks, super helpful

@ArthurFreitag 2 жыл бұрын

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

@CurbalEN 2 жыл бұрын

My pleasure!

@tonyhartigan4668 2 жыл бұрын

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

@CurbalEN 2 жыл бұрын

Gracias!!

@mohamedbahi4979 3 жыл бұрын

Thank you so much

@huubske9 2 жыл бұрын

Great vid, thanks

@joanneosborne2428 4 жыл бұрын

Thanks, Ruth. This is a great tip.

@CurbalEN 4 жыл бұрын

Really useful in many situations 😃😃 /Ruth

@mamdouhalsalamouny4309 2 жыл бұрын

Thank you great

@JamesFujitsu 8 ай бұрын

Thanks for this it really helped me

@BruceFonseca1 3 жыл бұрын

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

@rodneypearman8643 3 жыл бұрын

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

@CurbalEN 3 жыл бұрын

Do it!! You won't regret it!

@skulz66 3 жыл бұрын

Great video! thank u

@joseagundis1 4 жыл бұрын

Buen tip, gracias por compartir. Saludos Ruth

@CurbalEN 4 жыл бұрын

Saludos!!

@vicentemedel8469 Жыл бұрын

thanks you so much !!! hi from Chile!

@CurbalEN Жыл бұрын

Hola 👋

@EricaDyson 3 жыл бұрын

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

@rami765 3 жыл бұрын

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

@EricaDyson 3 жыл бұрын

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

@TheCiafm Жыл бұрын

Very usefull, Ty!!!

@MrAszpic 4 жыл бұрын

We definitely need more of this PQ tricks

@CurbalEN 4 жыл бұрын

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

@FredrikDahlberg Жыл бұрын

You are absolutely amazing!! 😍

@CurbalEN Жыл бұрын

🥳🥳

@ymohamed3154 Жыл бұрын

thanks you so much

@philipedesouzasantos1201 4 ай бұрын

Thank you miss

@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

@dontgetcaughtslippn6075 2 жыл бұрын

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

@CurbalEN 2 жыл бұрын

Great to hear!

@rendyshadows 2 жыл бұрын

always being great problem solver

@teenoou3132 4 жыл бұрын

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?

@CarlosEliezerHernandezPintor 3 жыл бұрын

Awesome trick! Very appreciated

@CurbalEN 3 жыл бұрын

One of my favorites!

@rajvihari4326 4 жыл бұрын

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

@CurbalEN 4 жыл бұрын

Thanks! /Ruth

@duskage99 2 жыл бұрын

that saved me alot of time ty!

@CurbalEN 2 жыл бұрын

You welcome!

@mittenwald3679 2 жыл бұрын

nice thanks again !!!

@scummmify 2 жыл бұрын

Ruth you are amazing!

@CurbalEN 2 жыл бұрын

You too!

@Henrik.Vestergaard 2 жыл бұрын

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

@CurbalEN 2 жыл бұрын

I use that method too :)

@locomotech6302 2 жыл бұрын

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!

@soy34mb 4 жыл бұрын

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

@CurbalEN 4 жыл бұрын

M is an amazing language 😍 /Ruth

@niranjansaikrishnac4394 4 жыл бұрын

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

@CurbalEN 4 жыл бұрын

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

@joebateman9668 4 жыл бұрын

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 4 жыл бұрын

It makes writing M cleaner, for sure!

@sarabashi1269 Жыл бұрын

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

@CurbalEN Жыл бұрын

Thank you for the feedback Sara ❤️

@rextkm 3 жыл бұрын

EXCELENT!!!

@database_tips_tricks 4 жыл бұрын

always waiting for your next videos such cool tips allways

@CurbalEN 4 жыл бұрын

Thanks!! 😊😊

@muhammadmuneebkhanafridi154 3 жыл бұрын

Thank you for thiss!!

@CurbalEN 3 жыл бұрын

Yay!

@MrDhunpagla 4 жыл бұрын

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

@CurbalEN 4 жыл бұрын

At your service :) /Ruth

@sandyhodge491 4 жыл бұрын

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

@CurbalEN 4 жыл бұрын

You welcome Sandy!

@klaustrampedach7312 2 жыл бұрын

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

@CurbalEN 2 жыл бұрын

Power query is wonderful!

@abhijeetghosh27 2 жыл бұрын

Super cool 👌👌

@sabkuchdhela191 2 жыл бұрын

thnks

@alexlijesen6197 2 жыл бұрын

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 |"

@michamachowski1627 2 жыл бұрын

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

@CurbalEN 2 жыл бұрын

Welcome!!

@butcher6939 2 жыл бұрын

wow thats awesome!

@CurbalEN 2 жыл бұрын

👏👏

@DIGITAL_COOKING 3 жыл бұрын

great one ! , 👍👍👍👍👍

@CurbalEN 3 жыл бұрын

Yey!!

@asjones987 3 жыл бұрын

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?

@renatorodriguesmedeiros9569 3 жыл бұрын

NICE!

@wasio44 3 жыл бұрын

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

@hcedenop 3 жыл бұрын

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

@CurbalEN 3 жыл бұрын

Team Power Query!!

@hcedenop 3 жыл бұрын

Curbal Yay! PQ&M 👍🏼

@sue_bayes 2 жыл бұрын

love it love it love it

@CurbalEN 2 жыл бұрын

💛💛💛

@abhilashg287 3 жыл бұрын

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'

@user-lm5wb8vi1x 3 ай бұрын

finished watching

@pahan95 3 жыл бұрын

Amazing!

@CurbalEN 3 жыл бұрын

🥳🥳

@snikanik 2 жыл бұрын

Brilliant 😎

@CurbalEN 2 жыл бұрын

🥳🥳

@TheRajinair 2 жыл бұрын

Very cool!

@CurbalEN 2 жыл бұрын

It is!!

@marjensendk 4 жыл бұрын

Great video! I've changed so many 1/0 to Yes/No the old way, just last week :(

@CurbalEN 4 жыл бұрын

Never again after this trick 😉😉 /Ruth

@faisalbaig9969 3 жыл бұрын

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.

@rodneypearman8643 4 жыл бұрын

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 4 жыл бұрын

Tips on how to do M? /Ruth

@selwynvanrooyen9432 2 жыл бұрын

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) ?

@donjoncho5878 2 жыл бұрын

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?

@wants77 2 жыл бұрын

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

@adambaker2463 3 жыл бұрын

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?

@o_felipecarvalho 3 жыл бұрын

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 3 жыл бұрын

Thanks! :)

@o_felipecarvalho 3 жыл бұрын

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 3 жыл бұрын

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

@o_felipecarvalho 3 жыл бұрын

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

@lastdance903 2 жыл бұрын

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.

@vida1719 4 жыл бұрын

Great tip to reduce number of steps. Would you know how much increase in performance can be achieved when combining steps and how would you check Query performance (any tools to check this)?

@CurbalEN 4 жыл бұрын

Yes, there is a query performance tool you can use inside power bi: m.kzfaq.info/get/bejne/pbyAippyzdS4gJs.html /Ruth

@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?

@davidferrick 2 жыл бұрын

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

@CurbalEN 2 жыл бұрын

Cleaner code, for sure!

2 жыл бұрын

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?

@monalymistry2010 3 жыл бұрын

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

@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?

@olaayorinde6865 3 жыл бұрын

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

@shashankchandramouli34 2 жыл бұрын

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 ?

@nainarastogi2096 2 жыл бұрын

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

@jorgerobertobalardingarcia6397 2 жыл бұрын

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.