IF Function in Power Query

The IF Function is the starting point of writing any conditional statement in Excel and so it is in Power Query. I consider IF in Power Query to be easier than Excel, purely because you can write IF and even Nested IF without using the formula bar, simply using the interface.

In this post I am going to show you 2 ways to write an IF, Nested IF and AND/OR function in Power Query using the interface and the formula editor

 

For Video Lovers!

 

Consider this Simple Data..

Using this data I have to solve for 3 questions

1. IF Marks are more than 40 then Pass else Fail
2. IF Marks are more than 70 AND attendance more than 70 then A Grade else B Grade
3. IF Marks < 40 OR Attendance < 40 then Fail else Pass

I will show you how to solve the above 3 using both the Interface (a.k.a Conditional Column) and by typing the Formula (a.k.a Custom Column)

 

IF using the Conditional Column Option

IF in Power Query can be very easily written using the conditional column option. You’ll find the Conditional Column option in the Add Columns Tab.

 

Let’s solve – IF Marks are more than 40 then Pass else Fail, using Conditional Column.

1. After loading the Data in Power Query
2. Click on Conditional Column
   • Select the Column Name as Marks
   • Operator as “is greater than or equal to”
   • Value as 40
   • Output as Pass
   • Else Fail

Note a couple of things

• The operator will show greater than / lesser than etc.. options only when the Column Name is a data type Number
• In the Value, Output and Else fields you can enter a text or select any other column

 

Again using Conditional Column let’s solve for – IF Marks are more than 70 AND attendance more than 70 then A Grade else B Grade. In this statement AND is used but instead we can smartly solve this using a NESTED IF using Conditional Column. Unfortunately the Conditional Column doesn’t have the option of writing the AND statement

• Note that I used the “less than” operator on Marks and Attendance first to get Grade B
• And assigned Grade A to the rest

 

IF using Custom Column

Now you’ll do pretty good with the conditional column option but the day is not too far when you’ll need to write a custom IF statement that doesn’t fit in right in Conditional Column option. A simple example could be, Multiply two numbers IF the result is TRUE

Now before I proceed you need to click on Custom Column in the Add Column Tab in Power Query window

 

Let’s Solve for IF Marks are more than 70 AND attendance more than 70 then A Grade else B Grade

I’ll create another Custom Column and the Syntax of IF remains the same in Power Query

= if <condition> then <true action> else <false action>

Notice a few things carefully

1. Unlike excel IF function uses lower case (if then else)
2. And there are no parenthesis “=IF()” or comma separators
3. You are supposed to write
   • if <condition>
   • then <true action>
   • else <false action>
4. Unlike excel you cannot leave the else part, if you want the else to do nothing you can write else null
5. You’ll have the write and multiple times for multiple conditions

 

Let try to solve for another problem that uses IF and OR function – IF Marks < 40 or Attendance < 40 then fail else pass

Notice Again

• if then else goes in lower case as discussed
• The or function also goes in lower case and you’ll have the write or multiple times to check for multiple conditions

 

Wrote any peculiar IF statement? Pour your thoughts in the comments !

 

 

More on Power Query

• SUMIF in Power Query
• Vlookup in Power Query
• Calculate Age in Power Query
• Custom Fiscal Year and Quarter in Power Query