Forum Discussion
Replace table value
I would like to replace the following
If column Artikelcode = 99980 or 99981 and column Artikelgroep = 170101 or 170103 then change column Artikelgroep to 170199
I have this formula, but strangely the first "or 170103" is not included in the change. What goes wrong?
= Table.ReplaceValue(#"Rijen gefilterd2", each [Artikelgroep], each if [Artikelgroep] = 170101 or 170103 and [Artikelcode] = "99980" or "99981" then 170199 else [Artikelgroep], Replacer.ReplaceValue,{"Artikelgroep"})
Thank you in advance
Actually this not strictly an issue with parentheses; even with the parentheses in the prior answer, the expression would cause an error. You must use an identifier for each and/or clause. This would be correct:
= Table.ReplaceValue(#"Rijen gefilterd2", each [Artikelgroep], each if ([Artikelgroep] = 170101 or [Artikelcode]= 170103) and ([Artikelcode] = 99980 or [Artikelcode] = 99981) then 170199 else [Artikelgroep], Replacer.ReplaceValue,{"Artikelgroep"})
--Nate
4 Replies
The issue with your formula is the way you have written the conditions. In the condition [Artikelgroep] = 170101 or 170103 and [Artikelcode] = "99980" or "99981", the or operator has a higher precedence than the and operator.
Therefore, the condition is evaluated as follows:
([Artikelgroep] = 170101) or (170103 and [Artikelcode] = "99980") or ("99981")
This is not what you intended. To fix this, you need to use parentheses to specify the order in which the conditions should be evaluated.
Here is the corrected formula:
= Table.ReplaceValue(#"Rijen gefilterd2", each [Artikelgroep], each if ([Artikelgroep] = 170101 or 170103) and ([Artikelcode] = "99980" or "99981") then 170199 else [Artikelgroep], Replacer.ReplaceValue,{"Artikelgroep"})
This will correctly evaluate the conditions as follows:
(([Artikelgroep] = 170101) or (170103)) and (([Artikelcode] = "99980") or ("99981"))Thank you very much!
You let me see how to approach the problem!
Actually this not strictly an issue with parentheses; even with the parentheses in the prior answer, the expression would cause an error. You must use an identifier for each and/or clause. This would be correct:
= Table.ReplaceValue(#"Rijen gefilterd2", each [Artikelgroep], each if ([Artikelgroep] = 170101 or [Artikelcode]= 170103) and ([Artikelcode] = 99980 or [Artikelcode] = 99981) then 170199 else [Artikelgroep], Replacer.ReplaceValue,{"Artikelgroep"})
--Nate
= Table.ReplaceValue(#"Rijen gefilterd2", each [Artikelgroep], each if List.Contains({170101,170103},[Artikelgroep]) and List.Contains({"99980","99981"},[Artikelcode]) then 170199 else [Artikelgroep], Replacer.ReplaceValue,{"Artikelgroep"})
