Home / Power Query / Power Query: IF Statement

Power Query: IF Statement

- Written by Puneet

Contents hide
Use IF Statement in Power Query
Using IF with ELSE-IF in Power Query (Nested IF)
Write an Actual IF Formula
Write Formula for Nested IF Statements
Points to Take Care while using IF in Power Query
Related Tutorials

Let’s take an example: You’re working on a sales report and need to check which transactions qualify for a discount. Any sale over $1000 gets a 10% discount.

Using IF Statement in Power Query, you can add a new column to your report that automatically shows “Yes” if the sale is over $1000, and “No” otherwise. This makes it easy to see which sales meet the discount criteria at a glance without manually checking each one.

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.

if-statement-in-power-query

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

Use IF Statement 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.
    use-if-in-power-query
  2. Click OK to convert the data into a table and load it into the power query editor.
    convert-data-into-table-and-load
  3. Go to the “Add Column” tab in the power query editor and click “Conditional Column”.
    click-conditional-column
  4. And when you click on the button, it will show you a dialog box to create the conditional statement.
    dialog-box-to-create-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.

    add-conditional-statement

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.

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.

if-with-else-if-in-power-query
  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.
    add-clause-to-get-else-if-statement
  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.
    use-greater-than-and-equal-to-get-may-be
  3. Once you click OK, you’ll get three different values in the ‘Status” column, according to your written IF statement.
    three-different-values-in-the-column

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.

write-if-formula-in-power-query

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.

refer-entire-columns-in-the-formula

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"
create-nested-if

In the end, click OK to add the column.

click-ok-to-add-cloumn

Write Formula for Nested IF Statements

In Power Query, nested IF statements can be used to handle multiple conditions in your data.

if [condition1] then [result1]
else if [condition2] then [result2]
else if [condition3] then [result3]
...
else [final result]

Let’s say you have employee sales data and want to test each employee’s performance based on their total sales. The performance categories are as follows:

  • “Excellent” for sales greater than $1000,
  • “Good” for sales between $500 and $1000,
  • “Average” for sales between $200 and $500,
  • “Below Average” for sales below $200.

Here’s how you could use nested if statements in Power Query to add a new column for these conditions: “Add Column” tab and select “Custom Column”. And then, in the custom column formula, use the below formula:

if [Sales] > 1000 then "Excellent"
else if [Sales] > 500 then "Good"
else if [Sales] > 200 then "Average"
else "Below Average"

Click OK to add the column and then apply the changes to load your transformed data into Excel or Power BI.

Points to Take Care while using IF in Power Query

Using the IF in Power Query for creating conditional columns is quite powerful, but there are a few limitations and challenges you might face:

  • Performance: If statements, especially nested ones, can slow down the performance of Power Query (Refresh). Each IF condition needs to be evaluated row by row, which can become time-consuming with large datasets.
  • Error Handling: IF statements do not handle errors by themselves. If your data includes nulls or errors, the IF statement might result in errors unless you handle these cases using functions like TRY or checking for nulls.

Related Tutorials