IF statement in PowerQuery (M) using values of 2 columns for a row ( Cant use AND)

[Anonymous]

Hi Everyone,

  I seem to be sleeping.. and strugling with something that should be fairly simple. I need to build a conditional column  that has to take in values dependent on a combination of existing values in 2 other colums

Column A        Column B   Column C
TM                       A              NOTOK
TM                       B               Super
TM                       C               Super
MS                       B                Nice
NB                       C                OK

In powerquery i can build

IF column A = MS then  Nice else if column A = NB then OK [ but i need here a combination of values: 
TM+B or TM+B = Super
TM+A= NOT OK

I can't seem to use "and" with or.. and it is late...maybe a hint will help

[edhans · Accepted solution]

This will do all but the "NB" logic, but I think you'll get the idea. You most definitely can use "and" but you cannot use the conditional column builder to do it. You'll need to build your formula manually in a custom column.

= if [Column A] = "MS" then "Nice" 
     else if [Column A] = "TM" and ([Column B] = "B" or [Column B] = "C") then "Super"
     else if [Column A] = "TM" and [Column B] = "A" then "NOTOK"
     else null

[edhans · Accepted solution]

This will do all but the "NB" logic, but I think you'll get the idea. You most definitely can use "and" but you cannot use the conditional column builder to do it. You'll need to build your formula manually in a custom column.

= if [Column A] = "MS" then "Nice" 
     else if [Column A] = "TM" and ([Column B] = "B" or [Column B] = "C") then "Super"
     else if [Column A] = "TM" and [Column B] = "A" then "NOTOK"
     else null

[Anonymous · Accepted solution]

Thanks... I will give it a try.. i hope this was just me sleepy 🙂