Replace Values Based on Condition (in a Single Step) in Power Query
HTML-код
- Опубликовано: 1 авг 2024
- This video teaches how you can replace values based on a condition. All in a single step! You can for example replace all negative numbers by 0, and leave the remaining numbers untouched. In addition, this method allows you to easily replace values based on another column.
WRITTEN BLOGPOST:
The ultimate guide to replacing values in Power Query, find it here:
gorilla.bi/power-query/replac...
Master Functions and Syntax in M
powerquery.how
If you want to learn how to do this for multiple columns, check out my other video: • Replace Values in Mult...
Chapters:
00:00 Introduction
00:16 The dataset
00:40 Creating a temporary helper column
01:21 Replace values based on a condition
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
SUPPORT MY CHANNEL
Any videos are made free of charge. You can support my channel by giving a donation through: paypal.me/rickmaurinus.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ruclips.net/user/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#ConditionalReplace #PowerQuery #BIGorilla
![[DOKKAN BATTLE] August Producer's Letter](http://i.ytimg.com/vi/JUTYpaV_4Ic/mqdefault.jpg)
Man! You saved me when ChatGPT and Copilot could not help, even in a task that seemed to be so simple.
Very thanks!
Thanks a ton for explaining that so clearly. I especially appreciate the elegance of doing it all in one step. Bravo!
Simple, elegant, and well explained. Thanks!
Thank you!
This video was so helpful! Exactly what I was looking for. I used criteria based on multiple fields for different scenarios. I used Each only once before the start of the if statements and it worked perfectly. Thank you!!!
Happy to read that! :)
Thank you for a great explanation and solution!
@BIGorilla comes through again (and again and again)! Thank you SO much!
🚀 Thanks Kimberly! glad to help
Absolutely brilliant, this has saved my so much time and head-scratching! Thank you 🙂
Cheers Stuart. This can be tricky !
Thanks, great explanation and very helpful.
This was really helpful. Thank you so much.
Man, you just helped me with this week's work. So nicely explained, cheers!
Good explanation make it easy to understand, thanks for sharing.
Thanks so much from Napoli !!
Thank you. Just what I was looking for.
wow! what a solution. Thanks a lot!!
Nice, direct and clear
Helpful and very good explained. Thank you.
Thank you !
Neat and wise approach, thx a bunch!!
Brilliant mate, this helped me so much. I have googled so much about this subject and couldn't find an easy way to do this, untill I stumbled upon your tutorial. Thanks!
Thanks Esther. There's an accompanying article where I explain some: gorilla.bi/power-query/replace-values/
Chef's kiss for this one Rick 🙂 I wish the Power Query UI options for updating columns were as good as for adding columns but this is a great explanation and helped me out. Subscribed!
Thanks, great to hear that helped!
Thank you mate, it was a big help to resolve an annoying bad clusterization problem (and ofc it saved me a lot of hours of research)
Ofcourse, and thanks for tuning in to the channel. Great to hear you managed to solve your clusterization problem.
^^ Rick
Simply Magic, Rick! Thanks!
Glad to hear!
Awesome trick! Thank you.
I am so pleased with how you explain and teach! Thank you! I have subscribed and liked the video!
WOW WOW WOWWW. Thank you so much for the support. It is highly appreciated. Thanks for coming to my channel!
Rick
This is really helpfull! exactly i wanted to learn. thank you so much !
That was exactly what I was looking for! Thank you 🙏
Thanks Man you are awesome, great way of presentation!
Awesome explanation.
Useful. Thank you👍
very helpful. many thanks
Me salvaste la vida, gracias por tu video!
This is BEAUTIFUL! Thanks dude, this vid helped a bunch!
great!
I love it. Thank You for making this video.
Thanks Omid. I'm glad it helps, more useful content is scheduled to release soon. If you wanna keep track, subscribe 😎🔥
Thanks so much, this is an amazing tip, very useful, was looking how to do this for ages. 👍🏽👍🏽👍🏽👍🏽👍🏽👍🏽
Very Clever, thank you
Note to self: To skip a line in the formula bar, press "Shift" + "Enter"
Great tip!
Awesome solution bro🎉
Awesome Rick!
Thank you.
I like to do a bulk replacement, based on table (excel) with two columns, A (numbers) and B (text).
If the value of Number columns is equal to a value in column A, then replace the text in another columns, with text from columns B.
Thanks great video
That is a fantastic trick. I have just used it. Thank you Rick! ;-)
Great Fajne, glad it worked!
Oh my gosh. Why WHY DID I NOT WATCH THIS YEARS AGO!!!
Nice one!
Thanks!
Awesome content!👍
Thank you Anthony! Glad you like it 😁
Excellent video. The single step code is very helpful. How would you modify the code that can query against another wild keyword From To table?
Very helpful video! You are a genius. Liked ans suscribed!
Thanks
I love you for this
Great video. How do you show the bar below the header withValid, Error, Empty status? Thank you!
Amazing
Great video, but what if I want to replace values based on matching condition of certain column values to column values from another table. (Like how we create relationships using keys)?
Great video! Is there any way to replace the values in various columns and not just one and each with the same condition?
I want to click a button and set cell value in a block from B3:H60 to “0” ONLY if the corresponding row cell in column A has a value(is not null)
And I cannot figure this out to save my life. Can you help?
How would you cycle through another table to determine the values to lookup and replace? I've seen the list.accumulate, however it did not work in my use case. Is there another way to do this?
Hi Gorilla Sir, Great Video
I was trying to apply to multiple columns but cannot get it work
Table.ReplaceValue(#"Change Type:,
each {[Value1], [Value2]},
each if [Name] = "Tommy" then "Replaced" else {[Value1], [Value2]},
Replacer.ReplaceValue, {"Value1", "Value2"}
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 ?
I have query
I have made a list of column which contains TDS in their heading.
Now I want to create a Column where it checks the column from this list for current row that if any column from this list is not null for the row then take the heading of the column from the list in the created column, if all column are null then take null .
Rick, love you useful youtube videos! Just went through your 150 text function examples and by far it is the most practical stuff I have seen on M code. But, I am not seeing something I believe exists in other languages, checking for a blank value. I see cells in a row which are not null and I would like to test for it, but I am not seeing a way to do it. Am I missing something?
Hey Peter. Bit late to the party. So you're looking to test if something is blank. Perhaps I'm misunderstanding but have you tried a statement like:
if [SomeColumn] = null then "null" else "non-null"
Thank you for sharing knowledge. Could you give more details on how to generate IndexYearMonth column ,
Hi Krishna,
I appreciate the support. To answer your question. The topic of an IndexYearMonth column was not mentioned in this video, but can be useful for time intelligence purposes.
To create one:
1. Group your data in Power Query by Year and Month, and as aggregation select 'All Rows'.
2. After clicking OK, go to 'add column' and add an Index Column.
3. Since you clicked All Rows in step 1, you still have table objects available on each line. Expand the column with table objects by selecting the arrows in the right-top of the column.
You have now created the YearMonth Index column. Hope it helps!
Rick
@@BIGorilla Thank you Rick. actually i am doing the order by for the last 12 months. having data like Feb-20 , Jan-21 ,... etc.
Hi @@krishnamurthy1945, that's perfectly fine. Is there a question or can go forward with it?
But Rick if have mutiple replacement like say 100 then I wouldn't perfer writing if else..can we use switch or any better way..
like + sub, to the point and well explained!
The benefits to this method are numerous. Happy it clicked!
Hi mate! Very useful video. I'm relatively new to Power BI/Power Query. I need to complete some missing data on my table, more exactly missing city names on addresses. Your code worked perfectly when I need to change one name to another, but it doesn't work when the field/column is blank or null. Any idea why?
Try changing code at the end to Replacer.ReplaceText to Replacer.ReplaceValue or vice versa
I keep having trouble making this work if the values I happen to be replacing are null. The condition I'm using is based on the value in another column, and it works just fine if do it by adding a conditional column. What am I doing wrong?
Hey Rick, does this break query folding or does it play nice? Any tips on extracting an hour from a date time stamp without breaking query folding? I couldn't work it out without turning the dts into a string, then using LEFT and RIGHT, which was very ugly...
Jaap!
Unfortunately, no additional tips for that.
Your approach works, extracting the time part with a Text.End( Text.From( [DateTime] ), 7 )... (I'm guessing somewhere around 7 characters).
You could alternatively duplicate your DateTime column, and simply format it as Time as Data Type in the Power BI interface, don't do it in PQ.
Ideally, create the column in your SQL database.
Hopefully the engine supports Query Folding for Time data types in the future, however, for now it you'll have to find another way :(
Cheers,
Rick
Have a query : In Power query a column has text and dates both. I want to replace only the dates to null so that i can fill down the text. can you pls guide ?
Something like:
if Value.Type( [Column] ) = "Text" then [Column] else null.
Please have a good look what Value.Type returns for your text values. :)
Excellent video.. thank you so much!
Could you tell me how to replace value when I have more than 1 condition (for example, I need to change the value for "India" under column Country on 17 December 2021 under Column "Date"
Thanks in advance
Best regards
You can extend the logic in the video. So add an if condition that says: if [country] = "India" and [Date] = #date(2021, 12, 17) then true else false
Please have a look at if-statements if you have any trouble there: gorilla.bi/power-query/if-function-in-power-query/
Great vide! But it does not work if the cell already has a null value
Great video - one question - What change would you make if you were interest in changing the value if [STATE] contained the letter "T" rather than [STATE] = "Tamaulipas" - I cannot quite seem to get it right when trying to use a Text.Contains statement
Hi Kathy, You can try something like:
each if Text.Contains( [STATE], "T" ) then x else y
Hope that helps!
@@BIGorilla For this to work, the word state needs to be proper case "State" which is the way the field name is shown in the video. I also tested a second condition with the script below and it works.
= Table.ReplaceValue(#"Sorted Rows",
each [Smoking_Allowed],
each if Text.Contains([State],"T") then "x" else
if Text.Contains([State],"E") then "y"
else [Smoking_Allowed],
Replacer.ReplaceText,{"Smoking_Allowed"})
Question: How do you deal with the lower case "t" which is not picked up in my example. I'd like it to test for upper or lower case letter T.
Great video. I struggled for hours 😀with the text contains function until I found this comment.
Thanks,
John
@@johnzurales8461 you can use the third optional argument of text . contains and fill in: Comparer.OrdinalIgnoreCase. This tests for your value case insensitive. Cheers 😁
Helpful. But what is the "No" is instead some column reference, will it work?
What if*
I like your solution! What about if a "contains" if formula? I will like to keep all values that contain Season (i.e. Season 1, Season 4, etc.) and delete everything else in my Season column. Total noob here.😁
try something like : if Text.Contains( [Season], "Season" ) then [Season] else null
Does this work for date columns? Didn't work for me
One column contains values as 8806182550997, 1005RN07. I want to replace the alphabets in the second value with 00 1) What data type should i keep for the column? How do i do it ? I tried to keep data type of column as Text, Replace function in power query is not able to recognize the text part????
Sir can we change multiple thing without using formula
how to display percentage of Valid , Error, Empty below header?
So, will the other States, other than Tamaulipa, continue with the original value? I notice that in the formula "if" the states different from Tamaulipa remain as "No" as original not changing to "Smoking Allowed"
Hey Felipe,
You are correct. Lines where the state is different from Tamaulipas, keep their original value. The original value was in a column named "Smoking Allowed". The formula therefore returns the original value of that column. The words 'smoking allowed' are not returned.
@@BIGorilla Thank you!!
Hi Rick, in my example it did not work when there was a null value in the column, after fixing that the magic was there 🙂
Just ran into the same issue. Thanks for pointing this out. Inserted a step above to replace nulls with zeroes and it started working. Thanks!
Note, that if you want to replace NULL values, you have to check the checkbox "Match entire cell contents"
For so long this has bothered me why this wouldn't play nice with null values but you are correct this resolves it! Thank you. It seems if you are having an issue with nulls (might extend to other data types also) change Replacer.ReplaceText to Replacer.ReplaceValue or vice versa.
You're right. null is not considered a text value, so Replacer.ReplaceValue is required. I explained some of that here:
gorilla.bi/power-query/replace-values/#replacer-functions
This trick is great but it does not seem to work with columns of type logical. Change the type to text first, then replace, then switch back.
Hi is there anyway to make the column name in the each clause a variable? In you example I wish to do something like myColumntoUpdare = {Smoking Allowed} and then ...each myColumnToUpdate...I have use cases where I don't know the column to update in advance and would like this to be dynamic
Good question, I'm not sure at this point...
As variant
listOfColumns = {"Smoking_Allowed"}, // could be few
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",
"Tamaulipas", //a
each [State], //b
(c, a, b) => if a = b then "No" else c,
listOfColumns ) //c
Hi, I need replace values on incremental value. E/g week 22 as week 1, week 23 as week 2 & so on. How to I perform the action in Powerbi
You can try something like:
if [weeknumber] >= 22 then [weeknumber] - 22 else [weeknumber] + 33
I'm not fully sure the +33 is correct, but some number should work there. Hope it helps!
@@BIGorilla it worked thanks ! "Week " & if >= 22 , -21)
any idea ,,some logic for SQL?
what if im replacing a date column?
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 |"
hi Alex,
From what you describe you can do that with the regular replace operation. No need for a conditional replacement. Just right click the column, replace values, and replace "->" by "|"
Hello :)
I do have a question.
Honestly - i found this solution somewhere else.
that said I also have an problem. when I operate on text values like you, it works perfectly.
But when in my equivalent of [State] column I do have an object like Table or List and I do a check if the Object is empty List.Isempty or Table.IsEmpty it stops working correctly and return the value that was originaly in this column.
why is that?
Can you help out?
I'm afraid I don't understand the question.
@@BIGorilla ok Let me try asking this way.
Imagine that instead of classical text / Value in [State] you do have and object nested Like another Table or List.
Now can I using Replace.Values function to change Table / List object into null value when Table or List is empty?
Can we Replace any Text Before a Delimiter in Power Query?
Like I have below Excel
Zone - MP
Region - Veg
Nepal
State-Bhtan
Dubai
In ExcelSheet, Find and Replace, We do like * - and Replace All
What to be Used in Power Query to Replace any Text or Value Like *
Please Help
Please Revert
I would like to give hundreds of "thumbs up" 👍🙂 but sorry ... there is only one 😞
When is the ‘each’ statement required in a formula.
Hey Jonathan,
The each expression has been put into place to make it easy to define simple functions. You could write a simple expression that needs a number as input and increments it by 1:
(Y) => Y + 1
You can write this the same by:
(_) => _ + 1
Microsoft created a shortcut to get here by using:
each _ + 1
So in the example I show, a good formulation would be:
(_) => if _[State] = "Taumaulipas" then "No" else [Smoking_Allowed].
Each makes the expressions more readable. It's good to know its a shortcut for the regular function.
We need the function in the replace argument, since we don't replace values by a single other value. We want to apply logic, and therefore need the function.
I have used this method before but came across a problem when my field name had a "/" or "\". It doesn't recognise the square bracket notation to reference the field name and using the underscore shorthand for "this column" didn't work either. Renaming the field with a space instead of the "/" fixed the issue.
PS: Nice "Buddy Christ" (Dogma) on your shelf behind 👍
Hi Rick, this trick seems to be really good but for some reason, it does not work for me. I carefully followed every step but my "Hospital Group" column keeps returning null values. Even after checking the "Match entire cell context" box. Any help? Here is my formula
each [Hospital Group],
each if [MAK Code] = "510115" then "Huron Perth Alliance - Stratford General Hospital Site Hospital Reporting Cluster"
else [Hospital Group],
Replacer.ReplaceText,{"Hospital Group"})
Hey Hector, Sorry to hear that.
Could you change your code from: Replacer.ReplaceText, to Replace.ReplaceValue? Give that a try. I've explained their uses here: gorilla.bi/power-query/replace-values/#replacer-functions
@@BIGorilla Hi Rick, thanks for the help and your quick answer. Not sure why is not working on my end. I have tried both methods (ReplaceText and ReplaceValue) but my Hospital Group column is still showing null for those values.
@@hectordelvillar1246 It's hard to troubleshoot with you without seeing an example. Maybe you could:
- Look on my site on the URL provided above to see working examples
- Test the data type of your column (perhaps null values are causing issues?)
- Provide a test query that shows the issue.
@@BIGorilla Hi Rick, not sure if you received my email but I sent you the details that show the issue (July 3rd). I hope I can hear back from you. Again, thank you for all the help!
I want to add a today function in this. I need if [state] = greater than today then "Completed" else "_". Can anyone help me with the code for this?
Today in power query you can make Wkth:
Date.From( DateTime.LocalNow() )
Make sure the comparison column is of Date type 😁
@@BIGorilla I really appreciate the response. I typed =if [State] < = Date.From(DateTime.LocalNow() ) Then "Yes" Else "_" it put error in every box. Where do I put the < symbol
@@isabellarocks1000
=if [State]
@@BIGorilla I had a space between
@@BIGorilla would it be possible to add 30 days from today?
00:29 You almost pronounce correctly jaja, are you using XML from Mexico to retrieve all this dataset?
I try to use each if Text.startwith or Text.Endwith to replace with condition but it not working
Thank you for sharing
When I try replace text valu nothing happen when I investigate it I found the original value show as below
Donald#(00A0)Duke