Replace Values in Multiple Columns Based on Condition in Power Query

This video teaches how you can replace values based on a condition. 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
How to replace values based on a condition for a single column? • Replace Values Based o... .
Chapters:
00:00 Introduction
00:17 The dataset and goal
00:48 Replace values single column
01:06 Replace values multiple columns
01:35 Conditionally replace value on a single column
03:24 Replace values in multiple columns based on condition
04:56 Restoring data types
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
kzbin.info?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#ConditionalReplace #PowerQuery #BIGorilla

[@suki9860]

Rick, All your tutorials, and even the specific topics you choose to focus on are all high-class Masterclasses! Thank you!

[@paulgallagher2987]

Very clever workaround. Also love that "Replace Value Type" trick. Thanks Rick. :D

[@pravinshingadia7337]

I love all these techniques you show - very clever. Love your videos.

[@dhyrumc1987]

Great Video! That worked like a charm! Thanks for taking the time to put this together.

[@tetianazakharenko2731]

Very inspirational! Learned a lot from you

[@baruchschwartz819]

Impressive ability to simplify and teach

[@eriknegron1047]

Big like from me. Already been utilizing the conditional replace values from your previous video and have updated my current reports to remove additional steps I had.

[@BIGorilla]

Fantastic Erik. If you liked this, make sure to check out my ultimate guide to replacing values. It contains some methods that I haven't recorded on KZbin yet which you may like! ^Rick gorilla.bi/power-query/replace-values/

[@Kceesiu]

Thx man :) I tride to fix my problem and when a watched yours film everything was simple :)

[@050224011]

worked a treat and saved me from creating new columns, thanks

[@karimallahwala7022]

Very helpful. Thank you very much

[@SharadNidaria]

Hi Rick, Thanks for your super educational videos on Power Query. I learned to conditionally replace values and put them in a custom column but I have a dataset and some condition like below. Condition: I have a column which *Contains* some text like "AIM", "SEO", "SELF SUPPLY" etc. and I want to create a new "Custom Column" based on this comdition and output the text as per my requirement. I want to do this within the power query. Request you to please provide your expertise and valuable inputs. Thanks.

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

[@alexrosen8762]

Extremely useful! Thanks :-)

[@BIGorilla]

🔥 glad it helps!

[@martinkeane8293]

Hi this is great! I have multiple columns that all should have the same rules I.e yes =1 n/a = 1.5 no =0. My problem is when following the steps I only see it on 2 conditions how do I add a third?

[@nlopedebarrios]

Very useful, thanks, I subscribed ;)

[@mnowako]

I like it! Thank you!

[@BIGorilla]

Thanks Mariusz! 🙌

[@williamarthur4801]

Can this method work if you have to transform all of the columns ?

[@YisselGaviria]

Thanks for sharing 🤓

[@BIGorilla]

Ofcourse! And more is coming. Next week about replacing case insensitive 😎😎

[@alterchannel2501]

this was really helpfull

[@skalsolutions]

Nice video!

[@BIGorilla]

Thank you !! appreciate the support Alfonso 🙌

[@ramonillarramendi3112]

Thank you very much. Excellent explanation and video. I'm stuck with a data set (from pdf) that has n tables n columns. I'm looking for text values that could be in any column. When using pivot, I'm getting "There were too many elements in the enumeration to complete the operation ". So ended up using the traditional Replace but instead of hardcoding Column names in replacer, took them from a previous step with Table.ColumnNames. Been trying to use Table.FindText function but haven't been able to nest it with a replace. Any ideas? Thanks a mil.

[@BIGorilla]

Hi. I think you could make the unpivot and pivot work like in the video. Could you elaborate on where you get a problem? Have a close look at how the video does it, I don't see a reason why it can't work.

[@axetro1578]

If you use unpivot, replace, and then pivot, I think It's returning in error because there's duplicate value in the "value" column after you replace them. You can try to add an index column before unpivoting

[@patockadavid9727]

Hi Rick, I run a test explained at 2:50 and it is working for me. I used "Table.ReplaceValue(#"Changed Type",[Overall_Rating], each if [Overall_Rating] < 5 then 5 else [Overall_Rating], Replacer.ReplaceValue,{"Overall_Rating","Service_Rating"})" and values were changed in both columns. Small update: It works ONLY if "Overall_Rating" and "Service_Rating" are with the same value. I have no clue why... 🤫🤔🤐

[@McIlravyInc]

Is there a way to use one condition to make different replacements in the other columns? If col A value is true, then change value in column D to yes and column E to 1 for example

[@nikulpatel1207]

Hello, I have one table in three columns. 1. Accepted pages 2. Non accepted pages. 3. Total pages While accepted pages are 0 and non accepted pages are 0 then replace accepted pages 0 with total pages count. I want to work in power query. Can you please help me?

[@CleitondaSilvaLeal]

can u helpe me, i need to replace punctuation on my dataset.

[@thembuluwodonaldnetshikulw8751]

Hi i have a table that has sales, and in that table I have two columns that tells you what sales is this and where it belongs,e.g I have a product columns were I have different products,then I have category that has on 3 cotagory, but now I want one category not to include one of the products, how do I update that, I can do it in the backend but I don't want to change that in the backend I want to change it in the front end

[@thembuluwodonaldnetshikulw8751]

So how do I use the power query if statements to do that

[@thembuluwodonaldnetshikulw8751]

So how do I use the power query if statements to do that

[@danilowalenta644]

Hi Rick, thanks for your nice videos. I managed to change the value in several columns without pivoting the table by writing "Table.ReplaceValue(#"Changed Type", true, each if [Overall_Rating] < 5 then 5 else true, Replacer.ReplaceValue,{"Overall_Rating", "Food_Rating", "Service_Rating"})" . Maybe you can check if this is also working for your example :)

[@patockadavid9727]

It doesn't work for me 🤔

[@alexrico7929]

Not working for me neither. This is how it looks the multiple columns attempt for me: = Table.ReplaceValue(AddBOMline_ids_Column, true, each if Text.Contains([Ingrediente], "Canister") then "" else [ID externo],Replacer.ReplaceText,{"ID externo","Ref Taxonomia","ID taxonomia"}) Though, following line works for single column: = Table.ReplaceValue(AddBOMline_ids_Column, each [ID externo], each if Text.Contains([Ingrediente], "Canister") then "" else [ID externo], Replacer.ReplaceText,{"ID externo"})

[@bayapareddyk7956]

How to filldown firstnonblank values in multiple columns based on condition.

[@BIGorilla]

Hey bayapa, Can you elaborate with example data and a desired outcome?

[@Learner1963]

I have done it in 12 lines of scripts for a whole year.

[@williamarthur4801]

Hi, I know this isn't using Table.Replace, but to work on multiple columns you can use Accumulate; ColumnNames = Table.ColumnNames(Source) List.Accumulate( ColumnNames, Source, (S, C)=> Table.TransformColumns( S, { C , each if Value.Type (_) = type number then "number" else _ } ) ) I've tried a similar construction using Table.Replace , but have not got it to work.

[@BIGorilla]

Thanks William. That’s a great template. If you want to use List.Accumulate with Table.ReplaceValue you could use: = List.Accumulate( { {"A","Ab"}, {"B", "Bc"}, {"C", "Cd"}, {"D","De"} }, [ ReplaceThis = null ,ReplaceFor = null ,MyTable = Source ] , ( state, current ) => [ ReplaceThis = current{0} ,ReplaceFor = current{1} ,MyTable = Table.ReplaceValue( state[MyTable], current{0}, current{1}, Replacer.ReplaceText,{"Value"} ) ] )[MyTable]

[@williamarthur4801]

@@BIGorilla Thank you for all your videos, I've learnt so much.

[@teaking1981]

I couldnt do it with the function table .replace the best I could do that seems to work is: let Source = Table.FromRows( { {1,2,2,2}, {2,5,5,5}, {3,2,5,10}, {4,6,6,6}, {5,2,5,2} }, type table [ID=Int64.Type,Overall=Int64.Type,Food=Int64.Type,Service=Int64.Type]), #"Cleaned Text" = Table.TransformColumns( Source, { {"Overall", each Replacer.ReplaceValue(_,_,if _

[@AlexusMaximusDE]

Okay my friend. I watched your video and the idea of pivoting a table for this made me angry so here is what I found after a lot of fiddling around. It's basically the extended version of the (x,y,z)=> if y then z else x which you mention in your blog post Looking at the Replace.ReplaceValue function you see it takes three arguments value, old and new. And it's a dumb little function that amounts to (value as any, old as any, new as any) as any => if value = old then new else old value is of course the cell value, old and new are passed through from Table.ReplaceValue. Using an inline function we can just substitue the Replacer.ReplaceValue function and do Table.ReplaceValue(#"Changed Type", null, null, (value as any, old as any, new as any) as any=> if value < 5 then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"}) Now that we are controlling the function and know what exactly is being passed and how, we can do whatever. For example we could pass the Record for the current row in new or old and only adjust the ratings for a specific restaurant doing this: Table.ReplaceValue(#"Changed Type", each _, null, (value as any, old as any, new as any) as any=> if value < 5 and old[Restaurant_ID] = 132663 then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"}) Of course for that we don't have to pass the entire record because we can put the boolean result of that comparison right into one one of the parameters like this Table.ReplaceValue(#"Changed Type", each [Restaurant_ID] = 132663 , null, (value as any, old as any, new as any) as any=> if value < 5 and old = true then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"}) or maybe we figured out that U1021 is the manager of 132663 and is leaving negative ratings for competing locations to make himself look better in compariso, so we give all negative reviews he is leaving to his own restaurant and assign them to a different user to cover our tracks Table.ReplaceValue(#"Changed Type", each _, null, (value as any, old as any, new as any) as any=> if old[Overall_Rating] < 5 and old[Consumer_ID] = "U1021" then if value ="U1021" then "U1337" else 132663 else value,{"Consumer_ID","Restaurant_ID"})