Power Query If Function: Syntax, Examples, and Tips

Power Query’s if is a conditional expression, not a function: write if condition then value_if_true else value_if_false. For example, in a custom column where each row has an Amount field, if [Amount] >= 500 then "Large" else "Small" labels amounts of 500 or more as Large and all others as Small.

What the Power Query if expression does

Power Query uses the M language to import, transform, and combine data. Its if expression evaluates a logical condition and returns one value when that condition is true and another when it is false. It is useful for creating categories, applying rules, flagging records, and replacing values according to business logic.

The word “function” is common in searches for this feature, but the distinction matters when writing M. You do not call if with parentheses like a function. Instead, it is a language expression with three required parts: the condition, the result after then, and the alternative after else. The condition must evaluate to true or false, and both result branches must be valid expressions.

Power Query evaluates the expression for each row when it appears in a custom column. The result can be text, a number, a date, a logical value, or another compatible value. The two branches should generally return the same kind of value, especially when the new column is intended to have a consistent data type.

Basic syntax and punctuation

The general pattern is:

if condition then value_if_true else value_if_false

For example, assume the current row contains a numeric Quantity field. This expression returns the text “Restock” when the quantity is below 10, and “Sufficient” otherwise:

if [Quantity] < 10 then "Restock" else "Sufficient"

In M, the keywords if, then, and else are lowercase. The expression must include both result branches; leaving out else causes a syntax error. Column names are written in square brackets, and text values are enclosed in straight double quotes. The comparison operator < means “less than”; other common operators include >, <=, >=, and =.

When entering an expression in the Custom Column dialog, type the expression itself without a leading equals sign. In the formula bar or Advanced Editor, an equals sign may appear as part of the complete query step. That difference can be confusing if you copy a full step into a box that expects only an expression.

Add an if expression as a custom column

In Excel for Microsoft 365 on Windows, open the Power Query Editor, select Add Column > Custom Column, enter a name for the new column, and type the M expression in the formula box. Select OK to preview the results. The column is calculated as part of the query and will be recalculated when the query refreshes.

Assume the source table has a numeric Amount column. To label each row based on a threshold, enter this in the Custom Column formula box:

if [Amount] >= 500 then "Large" else "Small"

A row with Amount equal to 500 returns Large; a row with Amount equal to 499 returns Small. The comparison includes 500 because the operator is greater than or equal to. If the boundary should not count, use > instead.

The Conditional Column command is another option when you prefer a form rather than typing M. Choose Add Column > Conditional Column, specify the column, operator, comparison value, and output for each rule, and set the final Else value. Power Query creates the corresponding transformation for you. Custom Column is more flexible for nested logic, calculations involving multiple fields, or functions that the dialog does not expose directly.

Examples with numbers, text, and dates

For each example, the bracketed field must exist in the current query, and its values should have the indicated type. Checking types in the editor helps prevent comparison errors.

Numeric category: With a numeric Score field, if [Score] >= 70 then "Pass" else "Review" returns Pass for 70 and above, and Review below 70.
Calculated threshold: With numeric Quantity and Unit Price fields, if [Quantity] * [Unit Price] >= 500 then "Check order" else "Routine" tests the row’s extended value. A row with quantity 10 and unit price 60 returns Check order because 10 × 60 = 600.
Text match: With a Status field containing text, if [Status] = "Paid" then "Complete" else "Open" assigns Complete to an exact match and Open to other values.
Date cutoff: With an OrderDate field whose values are dates, if [OrderDate] < #date(2025, 1, 1) then "Before 2025" else "2025 or later" separates dates before January 1, 2025 from that date and later dates.

M’s #date(year, month, day) notation creates a date value, so the cutoff is not dependent on the computer’s regional date display. Make sure OrderDate is typed as a date rather than text; a displayed date can still be stored as text in the source.

Multiple conditions and nested if expressions

Use the logical operators and and or when a rule depends on more than one test. Assume each row has a text Region field and numeric Amount field:

if [Region] = "West" and [Amount] >= 1000 then "Priority" else "Standard"

This returns Priority only when both conditions are true. To accept either of two regions, parentheses make the intended grouping clear:

if ([Region] = "West" or [Region] = "North") and [Amount] >= 1000 then "Priority" else "Standard"

For three or more outcomes, put another complete if expression in an else branch. Assume a numeric Score field:

if [Score] >= 90 then "High" else if [Score] >= 70 then "Medium" else "Low"

The first matching condition determines the result. This ordering matters: the 90-or-higher test must come before the 70-or-higher test, or high scores would already match the second condition and never reach the first. For many categories, consider whether a lookup table and merge would be easier to maintain than a long chain of nested conditions.

Handle nulls, errors, and mismatched types

Blank-looking values imported into Power Query may be represented as null. A comparison such as [Score] >= 70 is not a safe way to classify every row if Score can be null: a conditional expression requires a logical true-or-false condition, and missing data can lead to an error. Check for null first. With a nullable numeric Score field, use:

if [Score] = null then "Missing" else if [Score] >= 70 then "Pass" else "Review"

That expression returns Missing for a null score, Pass for a score of 70 or higher, and Review for a lower score. If empty text is also possible, remember that "" is not the same value as null; handle the two cases separately if both should count as missing.

A different problem occurs when a field contains values of incompatible types, such as numbers mixed with text. A numeric comparison may fail on a text value. The durable fix is often to inspect the source and set or clean the column’s data type before applying the condition. If a particular conversion may fail, M’s try expression can capture an error, but it should be used intentionally rather than hiding data-quality problems without review.

To investigate a failure, select the error cell in the Power Query preview and inspect its details. Check the step immediately before the custom column, the field’s type icon, and whether the source has unexpected values. Fixing the upstream type or value is usually clearer than adding more branches to compensate for inconsistent input.

Text matching, data types, and refresh behavior

Text equality is sensitive to spelling, spacing, and capitalization in many M comparisons. For example, "Paid" and "paid" should not be assumed to match. If capitalization should not affect the rule, normalize the value first. With a text Status field, an expression such as if Text.Upper([Status]) = "PAID" then "Complete" else "Open" compares an uppercase version of the field. Consider trimming unwanted spaces as well, because "Paid " contains a trailing space.

Power Query also assigns types to columns. Text results in an if expression should usually be text in every branch; returning a number in one branch and text in another can produce an inconsistent or unintended column type. You can set the new column’s type after creating it, or include an optional type argument when using the M function that adds a column.

A custom column is part of the query’s transformation steps, not a one-time edit to the imported worksheet. When the query refreshes, Power Query applies the rule again to the refreshed source data. This makes the expression useful for repeatable workflows, but also means that changing the source’s structure or field names can break a step that refers to those fields.

Power Query if versus Excel IF

Power Query if and Excel’s worksheet IF serve similar decision-making purposes, but their syntax and execution environments differ. In an Excel cell, a worksheet formula commonly looks like =IF(A2>=500,"Large","Small"). In a Power Query Custom Column box, the equivalent row-based expression is if [Amount] >= 500 then "Large" else "Small". M uses the words then and else, refers to fields with brackets, and does not use worksheet cell references such as A2.

Choose Power Query when the rule belongs in a repeatable import or cleanup process and should run during refresh. Use a worksheet formula when the result should live in the grid and respond directly to worksheet edits. A Conditional Column is a point-and-click way to create common Power Query rules; it does not turn the M expression into an Excel worksheet formula.

Frequently Asked Questions

Is if a function in Power Query?
No. It is a conditional expression in the M language. Write the condition followed by then and else, rather than calling if() with parentheses.

Can a Power Query if expression have more than two results?
Yes. Nest another complete if expression in an else branch to test additional conditions. Put more specific or higher-priority tests first so an earlier rule does not capture rows intended for a later one.

Why does Power Query say “Token Else expected”?
The expression may be missing the required else branch, or punctuation earlier in the expression may have stopped Power Query from parsing it correctly. Check that every if has a matching then and else, and that text uses straight quotation marks.

Can I return null from an if expression?
Yes. Use null as either result when a missing value is appropriate, for example if [Amount] < 0 then null else [Amount]. The resulting column may need an appropriate nullable type.

Should I use Conditional Column or Custom Column?
Use Conditional Column for straightforward rules you can describe through the dialog. Use Custom Column when you need nested tests, arithmetic, text functions, or other M expressions that the dialog cannot conveniently express.

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.