cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jct999
Helper III
Helper III

PowerQuery : Conditional replacement with some columns

Hello

 

I have a table with 4 columns : COL_1, COL_2, COL_3 and COL_4

 

For each row, I want to replace the value of the column COL_1 by the value of the column COL_2, only if the value of the value of column COL_3 is greater than the value of the column COL_4.

 

How can I do that ?

 

Regards

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @jct999 ,

 

First, select COL_1 in Power Query, go to the Transform tab, then select Replace Values.

Enter a dummy replacement in line with the data type of COL_1 e.g. if it's a numerical field then replace 123 with 456, or abc with cba if text etc.

Once you've done this, you can edit the code for this replacement to whatever you want. It'll look something like this:

= Table.ReplaceValue(previousStep,"abc","cba",Replacer.ReplaceText,{"COL_1"})

 

So, the argument that describes the value you want to find, in the above example it's "abc", change this argument to "each [COL_1]", like this:

= Table.ReplaceValue(previousStep, each [COL_1],"cba",Replacer.ReplaceText,{"COL_1"})

 

In the next argument, where it says "cba" in the example, this is where you will add your logic, something like this:

= Table.ReplaceValue(previousStep, each [COL_1], each if [COL_3] > [COL_4] then [COL_2] else [COL_1], Replacer.ReplaceText, {"COL_1"})

 

This should do the trick depending on your data type. If this doesn't seem to work, then change the next argument from "Replacer.ReplacText" to "Replacer.ReplaceValue".

 

Pete

View solution in original post

1 REPLY 1
BA_Pete
Super User
Super User

Hi @jct999 ,

 

First, select COL_1 in Power Query, go to the Transform tab, then select Replace Values.

Enter a dummy replacement in line with the data type of COL_1 e.g. if it's a numerical field then replace 123 with 456, or abc with cba if text etc.

Once you've done this, you can edit the code for this replacement to whatever you want. It'll look something like this:

= Table.ReplaceValue(previousStep,"abc","cba",Replacer.ReplaceText,{"COL_1"})

 

So, the argument that describes the value you want to find, in the above example it's "abc", change this argument to "each [COL_1]", like this:

= Table.ReplaceValue(previousStep, each [COL_1],"cba",Replacer.ReplaceText,{"COL_1"})

 

In the next argument, where it says "cba" in the example, this is where you will add your logic, something like this:

= Table.ReplaceValue(previousStep, each [COL_1], each if [COL_3] > [COL_4] then [COL_2] else [COL_1], Replacer.ReplaceText, {"COL_1"})

 

This should do the trick depending on your data type. If this doesn't seem to work, then change the next argument from "Replacer.ReplacText" to "Replacer.ReplaceValue".

 

Pete

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.