Replace table value

[marsclone]

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

[wdx223_Daniel]

= 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"})

[Anonymous · Accepted solution]

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

[RobertPaegelow]

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")) 

[marsclone]

Thank you very much! 

You let me see how to approach the problem!