Forum Discussion

aliciab425's avatar
aliciab425
Regular Visitor
4 years ago
Solved

Power Query IF and AND Statement with Multiple Conditions

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.   

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    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