Forum Discussion
Replacing values in multiple columns based on condition in Power Query
Hello,
I have a table like this:
| Date | plan | unplan | internal | external | production | day flag |
| 1/1/2020 | 0 | 0 | 50 | 70 | 6000 | Production Day |
| 1/2/2020 | 0 | 0 | 0 | 0 | 0 | Non-Production Day |
| 1/3/2020 | 0 | 0 | 100 | 200 | 1300 | Down Day |
| 1/4/2020 | 0 | 0 | 30 | 80 | 5000 | Production Day |
| 1/5/2020 | 0 | 0 | 120 | 150 | 1000 | Down Day |
| 1/6/2020 | 0 | 0 | 0 | 0 | 0 | Non-Production Day |
| 1/7/2020 | 0 | 0 | 0 | 0 | 0 | Non-Production Day |
if [day flag] = "Down Day" then replace [plan], [unplan], [internal] and [external] columns with null, else keep original values.
The result like this:
| Date | plan | unplan | internal | external | production | day flag |
| 1/1/2020 | 0 | 0 | 50 | 70 | 6000 | Production Day |
| 1/2/2020 | 0 | 0 | 0 | 0 | 0 | Non-Production Day |
| 1/3/2020 | null | null | null | null | 1300 | Down Day |
| 1/4/2020 | 0 | 0 | 30 | 80 | 5000 | Production Day |
| 1/5/2020 | null | null | null | null | 1000 | Down Day |
| 1/6/2020 | 0 | 0 | 0 | 0 | 0 | Non-Production Day |
| 1/7/2020 | 0 | 0 | 0 | 0 | 0 | Non-Production Day |
I use code:
#"Replaced Value1" = Table.ReplaceValue(Source, each [plan], each if [day flag] = "Down Day" then null else [plan],Replacer.ReplaceValue,{"plan"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [unplan], each if [day flag] = "Down Day" then null else [unplan], Replacer.ReplaceValue,{"unplan"}),
#"Replaced Value3" = ...
#"Replaced Value4" = ...
As you see, I have to repeat same logic 4 times for replace values of [plan], [unplan], [internal] and [external] columns. Actually, in my table, there are 20 columns need do that. I don't want to repeat this logic 20 times.
I was wondering if any better solution for my situation?
Thanks!
Hi, Anonymous
Based on the simulated data source and the data processing logic you provided, write the query code like this:
= Table.ReplaceValue(Source, each [day flag]="Down Day", null, (x,y,z)=> if y then z else x, List.Range(Table.ColumnNames(Source),1,4))If my code solves your problem, mark it as a solution
Hi, Anonymous
As what is suggested by ziying35 , I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may insert a new step as below. On your side, you may add all corresponding columns in {"plan","unplan","internal","external",...}.
= Table.ReplaceValue(Source, each [day flag]="Down Day", null, (x,y,z)=> if y then z else x, List.Select(Table.ColumnNames(#"Changed Type"), each List.Contains( {"plan","unplan","internal","external"},_) ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
14 Replies
Hi, Anonymous
Based on the simulated data source and the data processing logic you provided, write the query code like this:
= Table.ReplaceValue(Source, each [day flag]="Down Day", null, (x,y,z)=> if y then z else x, List.Range(Table.ColumnNames(Source),1,4))If my code solves your problem, mark it as a solution
Hey ziying35.
I included your solution in my blogpost at https://gorilla.bi/power-query/replace-values/.
However, I'm accustomed to working with Replacer.ReplaceValues and Replacer.ReplaceText. I don't fully understand your example.Can you elaborate on the inner workings of :
(x,y,z)=> if y then z else x?
Warm regards,
Rick
Simple understanding: the second parameter of the function Table.ReplaceValue is represented by y, the third parameter is represented by z, and the replaced element itself is represented by x
Hi ziying35 , Sorry for pinging you on this old topic. This method works perfectly if I just want to replace the value with a fixed value (null @ 1 @2, etc). But what if I want to multiple the old value with 1000? For example if the old value is 1000, instead of changing it to null i want to make it 1,000,000.
I tried changing the null to below, but it didn't work.
each _ * 1000Figured it out minutes after asking this.
= Table.ReplaceValue( Source, each [day flag]="Down Day", each _, // this step become redundant, but needed to fill the syntax requirement // (x,y,z)=> if y then z else x, --- this is the original one, below is edited. (x,y,z)=> if y then x*1000 else x, // we just ignore z, and replace it with x*1000 List.Range(Table.ColumnNames(Source),1,4) )
Hi, Anonymous
As what is suggested by ziying35 , I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may insert a new step as below. On your side, you may add all corresponding columns in {"plan","unplan","internal","external",...}.
= Table.ReplaceValue(Source, each [day flag]="Down Day", null, (x,y,z)=> if y then z else x, List.Select(Table.ColumnNames(#"Changed Type"), each List.Contains( {"plan","unplan","internal","external"},_) ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can include multiple columns in the list. This is a text replacement. Just add more columns in the list brackets {}
Table.ReplaceValue(#"Filtered Rows","a","g",Replacer.ReplaceText,{"Stock Item", "Color", "Selling Package"})Another way to do it, is the unpivot your columns, and then on the newly created column apply a conditional replace operation.
= Table.ReplaceValue( #"Changed Type", each [Value], each if [day flag] = "Down Day" then null else [Value], Replacer.ReplaceValue,{"Value"} )You can find the details on the conditional replace right here:
https://gorilla.bi/power-query/replace-values/#conditionally-replace-values
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
