Power Query: IF Statement

In Power Query, if you want to create an IF statement, there’s a specific option to help you write it without a formula. For example, below is a list of numbers (100) in column A.

You need a new column with the IF statement to check if a number is greater or equal to 1000.

Use IF in Power Query

Below are the steps to write an IF statement in Power Query:

1. First, select a cell, and go to the Data Tab > Get Data > From Other Sources > From Table/Range.
   
2. Click OK to convert the data into a table and load it into the power query editor.
   
3. Go to the “Add Column” tab in the power query editor and click “Conditional Column”.
   
4. And when you click on the button, it will show you a dialog box to create the conditional statement.
   
5. You need to follow below steps:
   1. Enter the New Column Name “Status”.
   2. Select the column “Numbers”.
   3. Use the Operator “is greater than or equal to”.
   4. Enter the Value “1000”.
   5. “Yes” for the Output.
   6. “No” for else.
   7. Click OK.
   
   

With the above IF statement, you tell power query to create a new column where use “Yes” if the number is a greater than or equal to the “1000”, else “No”. And the moment you hit enter, it enters a new column with the condition you have written.

Using IF with ELSE-IF in Power Query (Nested IF)

You also have the option to use the else if statement with the IF (nested). You can see an option to add it in the IF statement dialog box.

1. When you click on the “Add Clause” button, you get an Else IF statement just like IF, and now you need to enter details here.
   
2. Here, I will use greater than and equal to the operator to get the value “May Be” for numbers above 500 but below 1000.
   
3. Once you click OK, you’ll get three different values in the ‘Status” column, according to your written IF statement.
   

Write an Actual IF Formula

Yes, you can write an actual IF formula as well. For example, writing the IF formula in power query is slightly different in Power Query, but not much. So, once you load data into the power query editor, go to the Column Tab and click on the “Add Column” button.

And then, enter the below formula into the ” formula input bar”.

=if [Numbers] >= 1000 then "Yes" else "No"

There are three parts to this formula:

• if – condition to text
• then – value if the condition is true.
• else – value if the condition is false.

And make sure to write all these in small letters. Unlike Excel, in Power Query, you need to refer to the entire columns in the formula. And we also have greater than equal to the operator to test the condition.

And, if you want to create a nested if you can do this, just like below:

=if [Numbers] >= 1000 then "Yes" else if [Numbers] >= 500 then "May Be" else "No"

In the end, click OK to add the column.