topic Re: Replacing values in multiple columns based on condition in Power Query in Power Query

Replacing values in multiple columns based on condition in Power Query

Anonymous — Tue, 11 Aug 2020 22:38:39 GMT

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!

Re: Replacing values in multiple columns based on condition in Power Query

edhans — Tue, 11 Aug 2020 22:44:18 GMT

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

Re: Replacing values in multiple columns based on condition in Power Query

ziying35 — Wed, 12 Aug 2020 02:47:46 GMT

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

Re: Replacing values in multiple columns based on condition in Power Query

v-alq-msft — Wed, 12 Aug 2020 08:40:01 GMT

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.

Re: Replacing values in multiple columns based on condition in Power Query

Anonymous — Wed, 12 Aug 2020 17:11:09 GMT

Thank you very much! Your demostration is really very helpful for understanding the code. It works on my end!

Re: Replacing values in multiple columns based on condition in Power Query

Rickmaurinus — Tue, 25 Jul 2023 16:32:06 GMT

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.

Re: Replacing values in multiple columns based on condition in Power Query

Rickmaurinus — Sun, 26 Sep 2021 10:10:39 GMT

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

Re: Replacing values in multiple columns based on condition in Power Query

lbendlin — Mon, 27 Sep 2021 23:45:46 GMT

x = original value

y = match condition (true or false)

z = replacement value

Re: Replacing values in multiple columns based on condition in Power Query

ziying35 — Tue, 28 Sep 2021 06:55:31 GMT

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

Re: Replacing values in multiple columns based on condition in Power Query

Rickmaurinus — Mon, 04 Oct 2021 14:41:07 GMT

Hi ziying,

Thanks for adding the clarification. I could imagine in my head that happens. I'm just stumbled by how you thought of doing this, please help me understand.

No where does it say that Y represents the Table function, or that the third parameters should be the Z or what X should do.

If I can ask

1. How did you come up with this in the first place? Is there any documentation on this?

2. In theory, you could have replaced the function letters with any other letter, correct?

Best,

Rick

Re: Replacing values in multiple columns based on condition in Power Query

lbendlin — Mon, 04 Oct 2021 14:57:38 GMT

You can replace it with any string you want.

(original,logiccheck,alternative)=> if logiccheck then alternative else original

Yes, the documentation is a bit sparse on this. Here's a slight hint:

Replacer.ReplaceValue - PowerQuery M | Microsoft Docs

It's misleading, but at least it mentions parameters 🙂

I have a suspicion that this approach can be used for other M functions as well. Didn't have time to test it yet.

Re: Replacing values in multiple columns based on condition in Power Query

ziying35 — Tue, 12 Oct 2021 06:00:10 GMT

1. this use, I also happened to see in other places, I can not remember the place,.
2. Yes, there is nothing wrong, you can use any other letters instead

Re: Replacing values in multiple columns based on condition in Power Query

Anonymous — Fri, 22 Mar 2024 07:56:45 GMT

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

Re: Replacing values in multiple columns based on condition in Power Query

Anonymous — Fri, 22 Mar 2024 08:26:28 GMT

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

Re: Replacing values in multiple columns based on condition in Power Query

dufoq3 — Fri, 22 Mar 2024 10:53:39 GMT

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