PowerQuery Replace Values Based on Multiple OR Condition

[afmb]

Hello guys,

I'm trying to replace multiple values ​​of a column based on a multiple OR condition in a single step of Power Query.

This column should only have 4 correct values, for example: value1, value2, value3, value4 the others values I must replace with wrong value.

This is my formula, however it doesn't work correctly.

= Table.ReplaceValue(#"Changed type",
each [Column],
each if ([Column] <> "Value1" or [Column] <> "Value2" or [Column] <> "Value3" or [Column] <> "Value4") then "wrong value" else [Column],
Replacer.ReplaceText,{"Column"})

I also tried with this formula without success

= Table.ReplaceValue(#"Changed type",
each [Column],
each if ([Column] <> "Value1" then "wrong value"
else if [Column] <> "Value2" then "wrong value"
else if [Column] <> "Value3" then "wrong value"
else if [Column] <> "Value4" then "wrong value")
else [Column],
Replacer.ReplaceText,{"Column"})

I know there are other alternative solutions like a conditional column or with DAX, but I have to do it in PowerQuery without creating a new column.

Thanks for your help!

[AlexisOlson · Accepted solution]

No matter what text [Column] contain, your logic returns "wrong value" since C <> A or C <> B is always true unless A=B.

Suppose [Column] = "Value4". Then since "Value4" <> "Value1", your logic returns "wrong value".

Suppose [Column] = "Value1". Then since "Value1" <> "Value2", your logic returns "wrong value".

Try this instead:

= Table.ReplaceValue(
      #"Changed Type",
      each [Column],
      each if not List.Contains({"Value1", "Value2", "Value3", "Value4", "Value5"}, [Column])
           then "wrong value"
           else [Column],
      Replacer.ReplaceText,
      {"Column"}
  )

[wdx223_Daniel]

= Table.ReplaceValue(#"Changed type","","",(x,y,z)=>if List.Contains({"Value1","Value2","Value3","Value4"},x) then x else "wrong value",{"Column"})