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.
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 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 _ * 1000
Figured 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, there are always many ways. Here you can find record base solution. I'm refering to 1st post sample data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lFSAAIQDcKmIMIcRJgZGICogKL8lNLkksz8PAWXxEqlWB2QViNsWpGxX36eLladxth0GoJtMgKThsZgyiW/HFmbCTZtxiDCAuJq3G41xWojWMTQFGo7FhvNyPaiOTk6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #" plan " = _t, #" unplan " = _t, #" internal " = _t, #" external " = _t, #" production " = _t, #" day flag " = _t]), ColumnsTrim = Table.TransformColumnNames(Source, Text.Trim), ChangedType = Table.TransformColumnTypes(ColumnsTrim,{{"Date", type date}, {"plan", Int64.Type}, {"unplan", Int64.Type}, {"internal", Int64.Type}, {"external", Int64.Type}, {"production", Int64.Type}, {"day flag", type text}}), EnterColumnsToReplace = "plan, unplan, internal, external", ColsList = List.Transform(Text.Split(EnterColumnsToReplace, ","), Text.Trim), Ad_Replaced = Table.AddColumn(ChangedType, "Replaced", each if [day flag] = "Down Day" then Record.Combine({_, Record.TransformFields(Record.SelectFields(_, ColsList), List.Transform(ColsList, (y)=> {y, (x)=> x * 1000}))}) else _, type record ), Replaced = Table.FromRecords(Ad_Replaced[Replaced], Value.Type(ChangedType)) in Replaced
