Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello. I am having some issues replacing a numeric value based off 2 other columns:
= Table.ReplaceValue(#"Removed Columns",each [UPC Code],
each if [Distributor AB No]=2224625 or [Distributor AB No]=2225152 or [Distributor AB No]=2225418 and [UPC Code]=8066095774 then 8066095721 else [UPC Code],
Replacer.ReplaceValue,{"UPC Code"})
It should only replace the UPC Code to (8066095721) if it is equal to the "Distributor AB No" and UPC = 8066095774. However it is replacing other records with 8066095721. Any ideas and thank you.
Solved! Go to Solution.
The issue is that and condition is coupled with last or condition. You need to put bracket around your all or condition so that they are treated as one. Hence, following would work
= Table.ReplaceValue(#"Removed Columns",each [UPC Code],
each if ([Distributor AB No]=2224625 or [Distributor AB No]=2225152 or [Distributor AB No]=2225418) and [UPC Code]=8066095774 then 8066095721 else [UPC Code],
Replacer.ReplaceValue,{"UPC Code"})
Thank you very much. This has helped me greatly!
The issue is that and condition is coupled with last or condition. You need to put bracket around your all or condition so that they are treated as one. Hence, following would work
= Table.ReplaceValue(#"Removed Columns",each [UPC Code],
each if ([Distributor AB No]=2224625 or [Distributor AB No]=2225152 or [Distributor AB No]=2225418) and [UPC Code]=8066095774 then 8066095721 else [UPC Code],
Replacer.ReplaceValue,{"UPC Code"})