Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
= 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"})
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
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!
Check out the July 2025 Power BI update to learn about new features.