User Profile
Alicia_Bucci
Copper Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: Power Query Multiple IF Conditions in Custom Column
SergeiBaklan 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 T276KViews0likes1CommentRe: Power Query Multiple IF Conditions in Custom Column
Riny_van_Eekelen Thank you for the reply. I am struggling a bit with the difference from Excel IF function to PQ. Attached is a sample file I am working on. I was originally trying to work with 3 columns to create an IF formula but could not get it to work the way I needed it to, so I am now going to work with 2 columns, which is the original Excel formula I posted.77KViews0likes1CommentPower 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 me know what the Custom Column would be based on the below Excel calculation? =IF(M5=N5,M5,IF(AND(M5="BLANK1",N5="BLANK2"),"Investigate",IF(AND(M5<>"BLANK1",N5="BLANK2"),M5,IF(AND(M5<>"BLANK1",N5<>"BLANK2",M5<>N5),"300-Corporate",IF(AND(M5="BLANK1",N5<>"BLANK2"),N5,"ERROR"))))) Thank you in advance!Solved80KViews0likes8Comments
Recent Blog Articles
No content to show