Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi everyone,
I would like to give as an example the following query:
Index | Main Code | Sub Code |
1 | CP | CP1 |
2 | CP | CP2 |
3 | AR | AR1 |
4 | CP | CP3 |
5 | QS | QS1 |
I was wondering whether it would be possible to replace the values of Sub Code with "Other" if the values in Main Code are not equal to "CP". The table should look like this:
Index | Main Code | Sub Code |
1 | CP | CP1 |
2 | CP | CP2 |
3 | AR | Other |
4 | CP | CP3 |
5 | QS | Other |
Is this possible and what statement in Power Query could do the trick?
Solved! Go to Solution.
Hi @Anonymous ,
Yes, you can do this in Power Query by editing the M code generated by the Replace Value GUI.
Select your [Sub Code] field then go to Replace Values in the Transform tab.
When the dialog opens, just type any text (e.g. udsw etc.) in both the Find and Replace With boxes (don't need to be the same, just need to fill arguments).
Then edit the arguments where you can see the gobbledegook in the formula bar like this:
= Table.ReplaceValue(#"Changed Type", each [Sub Code], each if [Main Code] <> "CP" then "Other" else [Sub Code], Replacer.ReplaceText,{"Sub Code"})
This gives me the following ouput:
Pete
Proud to be a Datanaut!
Hi @Anonymous ,
Yes, you can do this in Power Query by editing the M code generated by the Replace Value GUI.
Select your [Sub Code] field then go to Replace Values in the Transform tab.
When the dialog opens, just type any text (e.g. udsw etc.) in both the Find and Replace With boxes (don't need to be the same, just need to fill arguments).
Then edit the arguments where you can see the gobbledegook in the formula bar like this:
= Table.ReplaceValue(#"Changed Type", each [Sub Code], each if [Main Code] <> "CP" then "Other" else [Sub Code], Replacer.ReplaceText,{"Sub Code"})
This gives me the following ouput:
Pete
Proud to be a Datanaut!
Hello!
How to say --> if not equal to "string1" or "string2" then "string3" please ? Thanks!
Hi @cher90 ,
It would be something like this:
if [yourField] <> "String 1" and [yourField] <> "String 2"
then "String 3"
else //your escape value e.g. null, "Error" etc.
Pete
Proud to be a Datanaut!