Forum Discussion
Replacing values in multiple columns based on condition in Power Query
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.
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.