Go to Add Column → Conditional Column. Name the column, pick the column to test, choose an operator such as is greater than or equal to, enter the value and the output, fill in Else and click OK. Click Add Clause for each extra else-if test.
To write it yourself, go to Add Column → Custom Column and enter =if [Sales] >= 1000 then "Yes" else "No". Keep if, then and else in lowercase; there are no commas or brackets as in Excel’s IF, and else is required.
For more than two outcomes, chain the tests: =if [Sales] > 1000 then "Excellent" else if [Sales] > 500 then "Good" else "Below Average". Power Query stops at the first test that is true, so put the highest threshold first. Combine conditions with lowercase and, or and not: =if [Region] = "North" and [Sales] >= 1000 then "Yes" else "No".
A blank cell arrives as null, and null >= 1000 makes that row show Error (We cannot convert the value null to type Logical). Test for it first: =if [Sales] = null then "No data" else if [Sales] >= 1000 then "Yes" else "No". Numbers stored as text also error, so set the column to a number type before you add the test.
Text comparisons are case-sensitive, so "north" won’t match "North"; compare Text.Lower([Region]) = "north" instead. To catch any other row-level error, wrap the test in try and otherwise: =try (if [Sales] >= 1000 then "Yes" else "No") otherwise "Check".
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.

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:
- First, select a cell, and go to the Data Tab > Get Data > From Other Sources > From Table/Range.

- Click OK to convert the data into a table and load it into the Power Query editor.

- Go to the “Add Column” tab in the Power Query editor and click “Conditional Column”.

- And when you click on the button, it will show you a dialog box to create the conditional statement.

-
You need to follow the below steps:
- Enter the New Column Name “Status”.
- Select the column “Numbers”.
- Use the Operator “is greater than or equal to”.
- Enter the Value “1000”.
- “Yes” for the Output.
- “No” for else.
- Click OK.

With the above IF statement, you tell Power Query to create a new column that shows “Yes” if the number is greater than or equal to 1000, else “No”. And the moment you click OK, it adds 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.

- 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.

- Here, I will use the “is greater than or equal to” operator with 500 to get the value “May Be” for numbers from 500 up to 1000 (1000 and above are already “Yes” from the first condition).

- 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. Writing the IF formula in Power Query is slightly different from Excel, but not much. So, once you load data into the Power Query editor, go to the “Add Column” tab and click on the “Custom Column” button.

And then, enter the below formula into the “Custom column formula” box (the equal sign is already there).
=if [Numbers] >= 1000 then "Yes" else "No"
There are three parts to this formula:
- if – condition to test
- 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 entire columns in the formula. And we have used the greater than or equal to 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.

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: go to the “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 the try … otherwise expression or by checking for nulls.
Use AND and OR in an IF Statement
To test more than one condition at the same time, use and or or between the conditions, in small letters. The following formula returns “Yes” only when the sale is 1000 or more and the region is “East”:
if [Sales] >= 1000 and [Region] = "East" then "Yes" else "No"
And with or, it returns “Yes” when any one of the conditions is true:
if [Region] = "East" or [Region] = "West" then "Yes" else "No"
Text comparisons in Power Query are case-sensitive, so “east” is not equal to “East”. To ignore the case, compare Text.Lower([Region]) with “east”.
Handle Nulls and Errors in an IF Statement
An empty cell comes into Power Query as null. Check for it first, so the rest of the condition only runs when there’s a value:
if [Sales] = null then "No Sale" else if [Sales] >= 1000 then "Yes" else "No"
And if the column can have errors, wrap the formula in try … otherwise, which returns the value after otherwise when the formula gives an error:
try (if [Sales] >= 1000 then "Yes" else "No") otherwise "Check"
Frequently Asked Questions
Why does my IF formula give a syntax error?
Usually, the keywords are in capital letters (IF, THEN, ELSE) or the else part is missing. In Power Query, every if needs a then and an else, and all three are written in small letters.
Should I use Conditional Column or Custom Column?
Conditional Column is easier for simple conditions on one column at a time. Use Custom Column when you need and/or, calculations inside the condition, or functions like Text.Contains.
Can I edit a conditional column later?
Yes. In the Applied Steps pane, click the gear icon next to the “Added Conditional Column” step to open the dialog box again and change the conditions.