Replace value of a column IF NOT equal to

[Anonymous]

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?

[BA_Pete · Accepted 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

[cher90]

Hello!

How to say -->  if not equal to "string1" or "string2" then "string3" please ? Thanks!

[BA_Pete]

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