Power Query Custom Column IF Statement: Examples

To write an IF statement in a Power Query custom column, use M’s if … then … else expression, such as if [Sales] >= 1000 then "High" else "Standard". In Excel for Microsoft 365 on Windows, open Power Query Editor, choose Add Column > Custom Column, enter the expression, and select OK.

What an IF statement does in Power Query

Power Query uses the M language to transform data before it is loaded to a worksheet or data model. An IF expression tests a condition for each row and returns one value when the condition is true and another when it is false. A custom column applies that expression across the table, producing a result for every row.

This is different from entering an Excel worksheet formula. In the Custom Column dialog, you refer to fields with square brackets, such as [Sales], rather than cell addresses such as B2. The expression is part of the query, so it is recalculated when the query refreshes and new or updated source rows are processed.

A basic expression has three required parts:

if introduces a condition that evaluates to true or false.
then gives the value to return when the condition is true.
else gives the value to return when it is false.

The keywords are lowercase and the else branch is required. Power Query does not use Excel’s IF() function syntax in a custom column.

Add an IF custom column in Excel

In Excel, select a cell in the source table or range, then use Data > From Table/Range to open Power Query Editor. If the query already exists, open it from the workbook’s Queries pane or the query list.
In Power Query Editor, select Add Column > Custom Column.
Enter a name for the new column, such as Sales band.
Type the M expression in the formula box. For example, use if [Sales] >= 1000 then "High" else "Standard" if the table has a numeric column named Sales.
Review the dialog’s available columns and error messages, then select OK. Check the results and set the new column’s data type if needed.
Select Home > Close & Load to return the query results to Excel, or use the appropriate load option for the workbook.

The Custom Column dialog expects the expression itself; do not start it with an equals sign or add each. In the full M query, Power Query represents a custom column using a function applied to each row. For example, the corresponding step can resemble Table.AddColumn(#"Changed Type", "Sales band", each if [Sales] >= 1000 then "High" else "Standard", type text). You usually do not need to write that full step in the dialog.

These ribbon paths describe Excel for Microsoft 365 on Windows. Power Query commands and availability can vary across Excel for Mac and Excel for the web, so consult the interface in the version you use; the M expression itself follows the same basic syntax.

Understand the syntax and column references

In an expression such as if [Quantity] > 10 then "Bulk" else "Regular", the condition is the comparison [Quantity] > 10. The square brackets identify a field in the current row. Power Query evaluates the comparison separately for each row, then puts either the text Bulk or Regular in the new column.

Use the data type that matches the condition. A number field can be compared with a number, a date with a date, and text with text. If a column name contains spaces or punctuation, Power Query may show it in a quoted field-reference form such as [#"Order Status"]. The simplest way to avoid spelling errors is to select the column name in the Custom Column dialog’s available-columns list.

When editing the complete M query, remember that M is case-sensitive for identifiers and text comparisons. The keywords must be written as if, then, and else. Text values need quotation marks, while numbers and date values are not quoted as text. The Custom Column dialog may automatically adjust the step’s output type, but it is still worth checking the type shown on the new column.

Use multiple conditions and outcomes

For more than two outcomes, place another IF expression in an else branch. This is called a nested IF or an else-if chain. For example, with numeric values in a column named Score, this expression assigns one of three labels:

if [Score] >= 90 then "A" else if [Score] >= 70 then "B" else "C"

Order matters. Power Query tests the first condition first. A score of 95 returns A without checking the later conditions; a score of 80 fails the first test and returns B; a score below 70 returns C. Put the most specific or highest-priority condition first, and make sure the final else covers every remaining case.

You can combine tests with the logical operators and and or. For example, with numeric Sales and text Region columns, this expression flags large West-region orders:

if [Sales] >= 1000 and [Region] = "West" then "Review" else "Normal"

Use parentheses when a condition mixes and and or so the intended grouping is clear. For instance, parentheses make it explicit whether a rule means “large sales in either region” or “large sales and one particular region.” For many categories, a long chain can become difficult to maintain; consider a lookup table and a merge instead.

Handle nulls, text, and data types

A blank value imported into Power Query is often represented as null, which is not the same thing as an empty text string. If a date field may be blank, check for null before using that field in later logic. With a column named ShipDate, an expression can label rows like this:

if [ShipDate] = null then "Pending" else "Shipped"

Do not assume that an empty string, a space, and null are interchangeable. A source system can contain all three. If text values may have inconsistent capitalization, normalize them before comparing. For a Region column that may contain different capitalization, use:

if [Region] = null then "Unknown" else if Text.Upper([Region]) = "WEST" then "West" else "Other"

The first test prevents the text-normalization function from being applied to a null value. Similarly, if a numeric column was imported as text, a numeric comparison may produce an error. Correct the type using the column’s type control or a Power Query transform step, rather than relying on text that merely looks like a number.

IF branches should return compatible kinds of values. Returning text in one branch and a number in the other may cause Power Query to assign a broad type or create issues in later steps. If the result is intended to be a label, return text in both branches; if it is a calculation, return numbers in both. Set the output type explicitly when downstream calculations or loading depend on it.

Common errors and how to fix them

Missing else: M requires a result for both true and false conditions. Add a final else, even if the fallback is a label such as "Other".
Using Excel syntax: IF(), cell references, and an equals sign at the start are not the usual Custom Column expression format. Write an M expression with field references in brackets.
Text and number mismatch: Comparing a number field to quoted text, or comparing text with a number, can cause an error or an unintended result. Check the column’s type and convert it deliberately.
Misspelled field name: A reference such as [Sale] fails if the field is actually named Sales. Choose the field from the dialog list or copy its displayed name accurately.
Unexpected nulls: A blank source value may not match the rule you expected. Add a null branch where blanks have a distinct meaning, and check whether the source contains empty strings too.
Wrong condition order: In a nested chain, an early broad condition can capture rows that were meant for a later category. Put priority rules first and inspect boundary values.

When the dialog displays an error, check the exact column name and data type before rewriting the whole expression. After it runs, filter the new column or inspect a few boundary rows—for example, values just below, equal to, and just above a threshold. That catches logical mistakes that do not generate technical errors.

Custom column versus conditional column

A custom column gives you direct access to M expressions, making it useful for combined tests, text functions, null checks, and calculations. A conditional column, created from Add Column > Conditional Column, provides a dialog for defining rules without typing the complete expression. It is often more approachable for straightforward cases such as “if Status equals Open, return Active; otherwise return Closed.”

Both approaches create a query transformation, not a live worksheet formula. Choose a conditional column when its rule builder clearly expresses the requirement and another person needs to review the logic visually. Choose a custom column when the rule needs more control or when you want to maintain the M expression directly. You can also create the rule with the dialog and inspect the generated step in the formula bar or Advanced Editor to learn how Power Query represents it.

Frequently Asked Questions

Does Power Query use IF or if then else?
In a custom column, use M’s expression form: if condition then value_if_true else value_if_false. It is not the Excel worksheet function form IF(condition, value_if_true, value_if_false).

Can a Power Query IF statement have more than two results?
Yes. Nest another if … then … else expression in an else branch. Test the rules in the intended priority order and include a final fallback for all remaining rows.

Do I type an equals sign in the Custom Column box?
No. Enter the M expression directly, such as if [Qty] > 5 then "Large" else "Small". The full query step may contain each, but the dialog supplies that row-by-row context.

How do I check whether a value is blank?
For a Power Query null, compare the field with null, for example if [ShipDate] = null then "Missing" else "Present". An empty text value is different, so inspect the source data if both kinds of blanks are possible.

Will a custom column update when the source changes?
Yes. The expression is part of the query and is recalculated when the query refreshes. It does not update the loaded results merely because the source changed; refresh the query to process the latest source data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.