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 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!
Solved! Go to 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"}
)
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"}
)
= Table.ReplaceValue(#"Changed type","","",(x,y,z)=>if List.Contains({"Value1","Value2","Value3","Value4"},x) then x else "wrong value",{"Column"})
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.