Forum Discussion
Replace Values based off two other columns OR and AND
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.
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"})
2 Replies
- Vijay_A_Verma
Most Valuable Professional
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"})