Power Query IF Examples: Conditional Logic in Excel

In Power Query, write an IF test in M as if condition then value_if_true else value_if_false. For example, if [Sales] >= 1000 then "High" else "Standard" labels each row according to its Sales value; you can enter this expression in a custom column in Excel’s Power Query Editor.

What an IF expression does in Power Query

Power Query is Excel’s data-import and transformation tool. Its formulas use the M language, which is different from the formulas you type into worksheet cells. M evaluates a condition for each row in a table, then returns one value if the condition is true and another if it is false. This is useful for categorizing records, flagging exceptions, and preparing data before it reaches a worksheet or data model.

The structure has three required parts: if, a condition, then and the value to return when the condition is true, followed by else and the value to return otherwise. M keywords are written in lowercase. Unlike an Excel worksheet formula, a custom-column M expression does not start with an equals sign.

In an expression such as [Sales] >= 1000, the square brackets refer to a column in the current row. The result is a new value for every row, not a single decision for the entire table. Column names with spaces can also appear inside brackets, as in [Order Status].

Add an IF expression as a custom column

In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range to open Power Query Editor. Then choose Add Column > Custom Column, enter a column name, and type an M expression in the formula box. The box accepts an M expression without a leading equals sign.

Choose a name that describes the output, such as Sales Category.
Enter an expression such as if [Sales] >= 1000 then "High" else "Standard".
Check the preview to confirm the result for several rows.
Choose OK, then check the new column’s data type and select Home > Close & Load when the transformation is ready.

The names in an expression must match the query’s column names. If a column has been renamed or removed in an earlier step, an expression that refers to its old name will fail. On Mac, Power Query’s available connectors and authoring features can vary by Excel release; the M expression itself follows the same syntax. Excel for the web does not provide the same full Power Query authoring experience as Excel for Windows.

Basic Power Query IF examples

These examples assume the named columns already exist in the query. Text results need double quotation marks; column references use square brackets. Select an appropriate data type for the new column after creating it.

Classify a number: with a numeric column named Sales, use if [Sales] >= 1000 then "High" else "Standard". A row with Sales of 1,250 returns High; a row with 800 returns Standard.
Flag a quantity: with a numeric column named Quantity, use if [Quantity] = 0 then "Out of stock" else "Available". A quantity of zero gets the first label; a positive quantity gets the second.
Replace a blank status: with a text column named Status, use if [Status] = null then "Not supplied" else [Status]. This returns a replacement label for a null value and preserves a populated status.
Compare a date: if Due Date has the Date data type, use if [Due Date] < #date(2026, 1, 1) then "Before 2026" else "2026 or later". The date literal uses the M form #date(year, month, day).

That last comparison uses a fixed cutoff, which is helpful when reproducing a report later. To compare against the current date instead, use Date.From(DateTime.LocalNow()) as the comparison value. The result then depends on when the query refreshes, so it can change over time; refresh environment and time-zone behavior can also matter for time-sensitive reports.

Combine conditions with AND, OR, and else if

Use and when all the conditions must be true, and or when either condition is enough. For example, with Region and Sales columns, if [Region] = "West" and [Sales] >= 1000 then "West priority" else "Other" assigns the priority label only when both tests pass.

For an either-or rule, this expression uses the same columns: if [Region] = "West" or [Region] = "East" then "Regional" else "Other". Parentheses can make a longer condition easier to read and help show how tests are grouped. For instance, use parentheses around an OR group when it is combined with another AND condition.

For more than two possible outcomes, chain conditions with else if. With a numeric Sales column, this expression creates three bands:

if [Sales] >= 5000 then "Large" else if [Sales] >= 1000 then "Medium" else "Small"

The order matters: Power Query checks the first condition, then checks the next only if the earlier one was false. A value of 6,000 returns Large; 2,000 returns Medium; and 500 returns Small. Put the most specific or highest-priority condition first so an earlier branch does not capture rows meant for a later one.

Handle nulls, text, and data types carefully

A null is not the same as an empty string, a space, or the number zero. A test such as [Status] = "" checks for empty text; it does not check for null. If a column can contain both, decide how each should be treated. For example, a status rule can check for null first and then check for an empty string in a later branch.

Text comparisons can also behave unexpectedly when capitalization varies. If values such as West, WEST, and west should count as the same label, normalize the text first. With a Region column, if Text.Upper([Region]) = "WEST" then "West region" else "Other" compares an uppercase version of the value. If the column might contain nulls, handle null before applying a text function.

Keep the return values compatible where practical. An expression that returns text in one branch and a number in another may produce a column with a broad or inconvenient type, complicating later steps. For a category, return text in every branch; for a calculation, return numbers consistently. You can inspect or set a column’s type using the type icon in its header or the Transform > Data Type command.

Conditional Column or Custom Column?

You do not always need to write M by hand. In Power Query Editor, choose Add Column > Conditional Column to build straightforward rules through a dialog. You can choose a column, an operator, a comparison value, and the output for each case. This is often easier for a small number of simple conditions and reduces the chance of a typing mistake.

Choose Custom Column when a rule needs functions, a calculation, several conditions, or logic that the dialog cannot express conveniently. You can also start with a Conditional Column and inspect the generated step in the formula bar or Advanced Editor to see its M implementation. Both approaches create a query transformation; neither changes the source data file by itself.

Common errors and how to fix them

“Token Else expected” or a syntax error: check that every if has a matching then and else. A chained rule needs else if between conditions.
Expression.Error: The field wasn’t found: confirm the column spelling and spaces, and check whether an earlier query step renamed or removed it.
A comparison or conversion error: inspect the column’s type and actual values. Numbers stored as text may need conversion before numeric comparisons; text that cannot be converted can cause errors.
Unexpected results with blanks: distinguish null, empty text, and zero, and add explicit branches for the cases that matter.
Rows falling into the wrong category: reorder chained tests and confirm that threshold boundaries use the intended operator. For example, > 100 excludes 100, while >= 100 includes it.

When a conversion may fail for some rows, M’s try ... otherwise pattern can provide a controlled fallback. For example, with an Amount column that may contain invalid numeric text, try Number.From([Amount]) otherwise null returns a number when conversion succeeds and null when it fails. Use this deliberately: turning every conversion problem into null can hide data-quality issues that should instead be reviewed.

What happens when the query refreshes?

An IF expression is saved as part of the query’s transformation steps. When the query refreshes, Power Query runs those steps again against the refreshed source data, applying the condition to the rows then present. This makes a query rule useful for recurring imports: you do not have to copy a worksheet formula down whenever new records arrive.

Power Query transformations are generally applied in sequence. If a step changes a column’s name or type, later steps use the updated version. If an IF step depends on an earlier cleanup step, keep that dependency in mind when rearranging or editing steps. After loading, review a refreshed result if the source format or data values have changed.

Frequently Asked Questions

Does Power Query have an IF function?
It has conditional logic, but the M syntax is an expression—if condition then value1 else value2—rather than an Excel-style IF() function. Enter the expression in a custom column or create a rule with the Conditional Column dialog.

Do I put an equals sign before an IF expression?
No. In the Custom Column dialog, enter the M expression without a leading equals sign. Excel worksheet formulas use a leading equals sign, but Power Query uses M syntax in its editor.

How do I write multiple IF conditions in Power Query?
Use else if for a sequence of possible outcomes, or combine tests with and and or. Add a final else to define what happens when none of the preceding conditions is true.

How do I test whether a value is blank?
For a null value, compare the column with null. For an empty text value, compare it with "". Check which kind of blank your source contains; they are different values and may need separate conditions.

Can I use an IF expression to replace values in an existing column?
Yes. You can create a conditional or custom column, then remove the original column and rename the replacement if that suits the transformation. Alternatively, use a replacement or conditional transformation where appropriate. Keeping a new column temporarily can make it easier to compare results before removing the original.

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.