Forum Discussion
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 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!
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"
Alicia_Bucci In PQ the manner of writing IF formulae is quite different from Excel's IF function. It would look something like this:
= try
if something then this else that
otherwise "error message"
where the bolded words are essential key words in the M-language. The non bolded words can be replaced by expressions, for example [Column1] >= 10, or just [Column2]and you can nest these if / then / else statements. How exactly is difficult to say without seeing your file.
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.
Alicia_Bucci Thanks, but can't work on this file. It links to a file on your local system.
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)
