PowerQuery : Conditional replacement with some columns

[jct999]

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

[BA_Pete · Accepted solution]

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