Forum Discussion
Solved
Power Query Multiple IF Conditions in Custom Column
Good morning, I have a formula calculated in Excel that I am now looking to calculate in Power Query. I know I can only use column reference while writing IF statements in PQ. Can you please let...
I'd simplify initial formula first, perhaps
=IF( M5=N5, M5, IF( M5="BLANK1", IF( N5="BLANK2","Investigate", N5), IF( N5="BLANK2", M5, IF( M5 <> N5, "300-Corporate", "ERROR" ) ) ) )with that
=if [M] = [N] then [M] else if [M] = "BLANK1" then if [N] = "BLANK2" then "Investigate" else [N] else if [N] ="BLANK2" then [M] else if [M] <> [N] then "300-Corporate" else "ERROR"
I'd simplify initial formula first, perhaps
=IF( M5=N5,
M5,
IF( M5="BLANK1",
IF( N5="BLANK2","Investigate", N5),
IF( N5="BLANK2",
M5,
IF( M5 <> N5,
"300-Corporate",
"ERROR" ) ) ) )
with that
=if [M] = [N]
then [M]
else
if [M] = "BLANK1"
then
if [N] = "BLANK2" then "Investigate" else [N]
else
if [N] ="BLANK2"
then [M]
else
if [M] <> [N]
then "300-Corporate"
else "ERROR"
SergeiBaklan Thank you for your help! It is greatly appreciated.
If I wanted to add "O5" to this formula, is that doable or will that create an issue with the formula?
I only tried to illustrate what Riny_van_Eekelen suggested and didn't test the formula. As for O5 - question is bit abstract, not clear in which part of formula you'd like to add it and what formula shall do with it.
Perhaps you may illustrate the logic if not with draft formula, when by diagram like
(again didn't test)
Here is the Excel IF statement, which gets more complex when adding AST3. My issue that I am running into is when I need to add AST4, AST5, and possibly AST6. Is there a better way to calculate this formula with adding the additional AST from 1-6? Would this be easier to formulate in Power BI?
IF(AND(A2="BLANK1",B2="BLANK2",C2="BLANK3"),"Investigate",IF(AND(A2=B2,A2=C2),A2,IF(AND(A2<>"BLANK1",B2="BLANK2",C2="BLANK3"),A2,IF(AND(A2=C2,B2="BLANK2"),A2,IF(AND(A2<>C2,A2<>"BLANK1",C2<>"BLANK3",B2="BLANK2"),"House",IF(AND(A2<>"BLANK1",B2<>"BLANK2",A2<>B2,C2="BLANK3"),"House",IF(AND(A2="BLANK1",B2<>"BLANK2",C2="BLANK3"),B2,IF(AND(A2="BLANK1",B2<>"BLANK2",B2<>C2),"House",IF(AND(A2="BLANK1",B2="BLANK2",C2<>"BLANK3"),C2,IF(AND(A2<>"BLANK1",B2<>"BLANK2",C2<>"BLANK3",A2<>B2,B2<>C2),"House",IF(AND(A2="BLANK1",B2=C2),B2,"ERROR")))))))))))
Here is the illustration of how this is laid out in Excel. I included a copy of the file as well.
AST1 AST2 AST3 Expected Result Line Item Formula Comp Territory BLANK1 BLANK2 BLANK3 Investigate Investigate Investigate T1 T1 T1 T1 T1 T1 T1 BLANK2 BLANK3 T1 T1 T1 T1 BLANK2 T1 T1 T1 T1 T1 BLANK2 T2 House House House T1 T2 BLANK3 House House House BLANK1 T2 BLANK3 T2 T2 T2 BLANK1 T2 T1 House House House BLANK1 BLANK2 T2 T2 T2 T2 T1 T2 T3 House House House BLANK1 T2 T2 T2 T2 T2
