Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
82 | |
74 | |
66 |
User | Count |
---|---|
121 | |
105 | |
102 | |
82 | |
72 |