Replace Values Based on Condition (in a Single Step) in Power Query

[@KaioVilasBoas-j2i]

Man! You saved me when ChatGPT and Copilot could not help, even in a task that seemed to be so simple. Very thanks!

[@AP-eb8hd]

chatgpt and copilot are useless for these kind of things..,, it helps people just start with basic things.

[@mommalongleggs7896]

Thank you so much for this video. This was the only source I found to help me solve my problem. One thing I did learn...when you are doing the each if line to make the replacement, the value you are replacing cannot be null. I added a step prior to replace all my nulls with "No Value" and then your solution worked like a charm!

[@xyz123770]

Thanks a ton for explaining that so clearly. I especially appreciate the elegance of doing it all in one step. Bravo!

[@reachmaqsood]

So far the best lesson on power query with all the details to small practical tricks.

[@adyelencu]

Man, you just helped me with this week's work. So nicely explained, cheers!

[@RaisingBrows]

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

[@BIGorilla]

Happy to read that! :)

[@milossubak8909]

Simple, elegant, and well explained. Thanks!

[@BIGorilla]

Thank you!

[@CAKimberlyLewis]

@BIGorilla comes through again (and again and again)! Thank you SO much!

[@BIGorilla]

🚀 Thanks Kimberly! glad to help

[@walkurey]

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!

[@BIGorilla]

Thanks Esther. There's an accompanying article where I explain some: gorilla.bi/power-query/replace-values/

[@carolineabrahamsson9107]

Thank you for a great explanation and solution!

[@lubomirjagersky7327]

Helpful and very good explained. Thank you.

[@BIGorilla]

Thank you !

[@balrajvirdee1087]

Thanks so much, this is an amazing tip, very useful, was looking how to do this for ages. 👍🏽👍🏽👍🏽👍🏽👍🏽👍🏽

[@moyura2]

Good explanation make it easy to understand, thanks for sharing.

[@yasinwarsame3282]

I am so pleased with how you explain and teach! Thank you! I have subscribed and liked the video!

[@BIGorilla]

WOW WOW WOWWW. Thank you so much for the support. It is highly appreciated. Thanks for coming to my channel! Rick

[@ahmadahmadsalim7023]

This was really helpful. Thank you so much.

[@users-9pR2s7]

That was exactly what I was looking for! Thank you 🙏

[@philipedesouzasantos1201]

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)

[@BIGorilla]

Ofcourse, and thanks for tuning in to the channel. Great to hear you managed to solve your clusterization problem. ^^ Rick

[@PeteMackay]

Thanks, great explanation and very helpful.

[@dercip8087]

Really helpful, Thank you!

[@tantewalla]

This is BEAUTIFUL! Thanks dude, this vid helped a bunch!

[@BIGorilla]

great!

[@smahmudmobin]

Great video. How do you show the bar below the header withValid, Error, Empty status? Thank you!

[@arne.munther]

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.

[@russomarta9059]

Thanks so much from Napoli !!

[@w13ken]

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!

[@BIGorilla]

Thanks, great to hear that helped!

[@philkrohn6335]

Great video! Is there any way to replace the values in various columns and not just one and each with the same condition?

[@stuartproctor6051]

Absolutely brilliant, this has saved my so much time and head-scratching! Thank you 🙂

[@BIGorilla]

Cheers Stuart. This can be tricky !

[@samyoung6331]

Oh my gosh. Why WHY DID I NOT WATCH THIS YEARS AGO!!!

[@evelic]

Thank you. Just what I was looking for.

[@ThiagoGSilva]

Nice, direct and clear

[@Soulenergy31]

Neat and wise approach, thx a bunch!!

[@huseyinburaktasci1638]

wow! what a solution. Thanks a lot!!

[@mariangelesavalos9130]

Me salvaste la vida, gracias por tu video!

[@nelson_k_d]

Thanks Man you are awesome, great way of presentation!

[@Sangram2031]

Awesome solution bro🎉

[@mnowako]

Awesome trick! Thank you.

[@suki9860]

Simply Magic, Rick! Thanks!

[@BIGorilla]

Glad to hear!

[@mapi55555]

Awesome explanation.

[@omidgharipour6974]

I love it. Thank You for making this video.

[@BIGorilla]

Thanks Omid. I'm glad it helps, more useful content is scheduled to release soon. If you wanna keep track, subscribe 😎🔥

[@andyle1256]

Note to self: To skip a line in the formula bar, press "Shift" + "Enter"

[@BIGorilla]

Great tip!

[@Easy_crafts_fr_u]

Useful. Thank you👍

[@luislopez-g2k]

Very helpful video! You are a genius. Liked ans suscribed!

[@mdmizanurrahman2914]

This is really helpfull! exactly i wanted to learn. thank you so much !

[@fajnefajne4339]

That is a fantastic trick. I have just used it. Thank you Rick! ;-)

[@BIGorilla]

Great Fajne, glad it worked!

[@krishnamurthy1945]

Thank you for sharing knowledge. Could you give more details on how to generate IndexYearMonth column ,

[@BIGorilla]

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

[@krishnamurthy1945]

@@BIGorilla Thank you Rick. actually i am doing the order by for the last 12 months. having data like Feb-20 , Jan-21 ,... etc.

[@BIGorilla]

Hi @@krishnamurthy1945, that's perfectly fine. Is there a question or can go forward with it?

[@mhd2hamad]

very helpful. many thanks

[@PillsLifestyleReviews]

Thank You!

[@vashisht1]

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

[@DavesMegaBeats]

Thanks great video

[@isaacmuhanga]

Helpful. But what is the "No" is instead some column reference, will it work?

[@isaacmuhanga]

What if*

[@joncue0304]

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?

[@kyleropp6634]

Thanks!

[@kathybunch2439]

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

[@BIGorilla]

Hi Kathy, You can try something like: each if Text.Contains( [STATE], "T" ) then x else y Hope that helps!

[@johnzurales8461]

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

[@BIGorilla]

@@johnzurales8461 you can use the third optional argument of text . contains and fill in: Comparer.OrdinalIgnoreCase. This tests for your value case insensitive. Cheers 😁

[@steveschouten]

Awesome Rick!

[@patrickpalombo]

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?

[@andresjustinianod.7067]

Thanks

[@050224011]

Very Clever, thank you

[@fachrulrizky]

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

[@BIGorilla]

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/

[@JorgePerez-bu4ph]

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?

[@Timbollew]

Try changing code at the end to Replacer.ReplaceText to Replacer.ReplaceValue or vice versa

[@rahulbhanushali5278]

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

[@wowagt4589]

I love you for this

[@10ozGold]

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?

[@hiteshraja76]

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 ?

[@BIGorilla]

Something like: if Value.Type( [Column] ) = "Text" then [Column] else null. Please have a good look what Value.Type returns for your text values. :)

[@Donzell1Dawson]

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

[@BIGorilla]

try something like : if Text.Contains( [Season], "Season" ) then [Season] else null

[@christopherskyap8985]

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

[@AP-eb8hd]

Hey man awesome

[@Gilligan2755]

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?

[@MayurPatil-d4o]

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 .

[@stevedepeijper5054]

Nice one!

[@Griffindor21]

Great vide! But it does not work if the cell already has a null value

[@smartwork4768]

Amazing

[@gezin_longuevilleosaer4245]

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 🙂

[@lucaspatterson4033]

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!

[@akshaypatil8155]

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

[@nikhilashishitlive8582]

Sir can we change multiple thing without using formula

[@pkuchnicki]

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?

[@BIGorilla]

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"

[@nickvanmaele8059]

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.

[@anthonyaroni5530]

Awesome content!👍

[@BIGorilla]

Thank you Anthony! Glad you like it 😁

[@arukhush7024]

Does this work for date columns? Didn't work for me

[@srikantmukherjee1034]

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

[@srikantmukherjee1034]

Please Revert

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

[@TehKonnos]

Note, that if you want to replace NULL values, you have to check the checkbox "Match entire cell contents"

[@Timbollew]

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.

[@BIGorilla]

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

[@sophykeo9639]

how to display percentage of Valid , Error, Empty below header?

[@felipesignorellireis7839]

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"

[@BIGorilla]

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.

[@felipesignorellireis7839]

@@BIGorilla Thank you!!

[@jaapolsthoorn796]

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

[@BIGorilla]

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

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

[@BIGorilla]

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

[@ANDRENASUUKYI]

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

[@BIGorilla]

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!

[@ANDRENASUUKYI]

@@BIGorilla it worked thanks ! "Week " & if >= 22 , -21)

[@ExcelInstructor]

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?

[@BIGorilla]

I'm afraid I don't understand the question.

[@ExcelInstructor]

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

[@JonathanExcels]

When is the ‘each’ statement required in a formula.

[@BIGorilla]

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.

[@williamrhopkins]

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

[@BIGorilla]

Good question, I'm not sure at this point...

[@sergeiStPetebaklan]

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

[@raphaellebeau6368]

like + sub, to the point and well explained!

[@BIGorilla]

The benefits to this method are numerous. Happy it clicked!

[@rakeshsahoo16]

any idea ,,some logic for SQL?

[@hectordelvillar1246]

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

[@BIGorilla]

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

[@hectordelvillar1246]

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

[@BIGorilla]

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

[@hectordelvillar1246]

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

[@lorenlim2315]

what if im replacing a date column?

[@workstuff5253]

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 👍

[@KlausEltrop]

I would like to give hundreds of "thumbs up" 👍🙂 but sorry ... there is only one 😞

[@isabellarocks1000]

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?

[@BIGorilla]

Today in power query you can make Wkth: Date.From( DateTime.LocalNow() ) Make sure the comparison column is of Date type 😁

[@isabellarocks1000]

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

[@BIGorilla]

@@isabellarocks1000 =if [State]

[@isabellarocks1000]

@@BIGorilla I had a space between

[@isabellarocks1000]

@@BIGorilla would it be possible to add 30 days from today?

[@edgarsan71991]

00:29 You almost pronounce correctly jaja, are you using XML from Mexico to retrieve all this dataset?

[@gettokeroro5007]

I try to use each if Text.startwith or Text.Endwith to replace with condition but it not working

[@Faisal63045]

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