Forum Discussion

Alicia_Bucci's avatar
Alicia_Bucci
Copper Contributor
Dec 06, 2021
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...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 06, 2021

    Alicia_Bucci 

    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"

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Dec 06, 2021

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.

Alicia_Bucci's avatar
Alicia_Bucci
Copper Contributor
Dec 06, 2021

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. 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 06, 2021

    Alicia_Bucci Thanks, but can't work on this file. It links to a file on your local system.

Resources