Power Query IF and AND Statement with Multiple Conditions

[aliciab425]

Good evening,

I am still new to Power Query and haven’t created many custom formulas. Below is an Excel formula I need assistance recreating in Power Query.

1. This formula is used to determine the comp territory

=IF(AND(P3="BLANK1",Q3="BLANK2",R3="BLANK3"),"Investigate",IF(AND(P3=Q3,P3=R3),P3,IF(AND(P3<>"BLANK1",Q3="BLANK2",R3="BLANK3"),P3,IF(AND(P3=R3,Q3="BLANK2"),P3,IF(AND(P3<>R3,P3<>"BLANK1",R3<>"BLANK3",Q3="BLANK2"),"300-Corporate",IF(AND(P3<>"BLANK1",Q3<>"BLANK2",P3<>Q3,R3="BLANK3"),"300-Corporate",IF(AND(P3="BLANK1",Q3<>"BLANK2",R3="BLANK3"),Q3,IF(AND(P3="BLANK1",Q3<>"BLANK2",Q3<>R3),"300-Corporate",IF(AND(P3="BLANK1",Q3="BLANK2",R3<>"BLANK3"),R3,IF(AND(P3<>"BLANK1",Q3<>"BLANK2",R3<>"BLANK3",P3<>Q3,Q3<>R3),"300-Corporate",IF(AND(P3="BLANK1",Q3=R3),Q3,IF(AND(P3=Q3,R3="BLANK3"),P3,IF(AND(P3=Q3,P3<>R3,R3<>"BLANK3"),"300-Corporate",IF(AND(P3<>"BLANK1",P3<>Q3,Q3=R3),"300-Corporate","ERROR"))))))))))))))

Thank you in advance for your assistance.   

[AnkitKukreja]

Hi! @aliciab425 

It would be easy to write the formula if you can share the sample data and expected result.

[BA_Pete · Accepted solution]

Hi @aliciab425 ,

I'm not going to completely rewrite the formula, but here is the structure and terminology you need to do this in Power Query:

if [AST Terr 1] = "BLANK1" and [AST Terr 2] = "BLANK2" and [AST Terr 3] = "BLANK3" then "investigate"
else if [AST Terr 1] = [AST Terr 2] and [AST Terr 1] = [AST Terr 3] then [AST Terr 1]
else if [AST Terr 1] <> "BLANK1" and [AST Terr 2] = "BLANK2" and [AST Terr 3] = "BLANK3" then [AST Terr 1]
else if...
...
else //your escape value here e.g. null, "ERROR" etc.

Any logical operators (+, *, <, > etc.) can be used in exactly the same way as in Excel.

Pete