In Power Query, write a nested conditional as if condition1 then result1 else if condition2 then result2 else finalResult. Add it in Excel for Microsoft 365 by choosing Add Column > Custom Column in the Power Query Editor; the conditions are checked in order, and the first true condition supplies the result.
What a nested if does in Power Query
Power Query uses the M language, whose conditional expression has the form if ... then ... else .... An if expression must have all three parts: a logical condition, a result for when that condition is true, and an else result for when it is false. To test another condition, put another complete if ... then ... else expression in the first expression’s else branch.
This structure is useful when a row needs to be classified using several rules. For example, an order might be marked as urgent if its value is high, standard if it passes a lower threshold, and small otherwise. Nested conditions are evaluated from top to bottom. Once a condition is true, Power Query returns its result and does not use the later branches for that row.
Unlike an Excel worksheet formula, M code does not start with an equals sign. It is also not Excel’s IF function: use the lowercase M keywords if, then, and else, and use square brackets to refer to fields in the current row.
Basic syntax and a working example
Suppose the query has a numeric column named Amount, and you want to label each row as Large, Standard, or Small. In the example below, Amount may also be null, so the first condition handles missing values before comparing the number.
if [Amount] = null then "No amount"
else if [Amount] >= 1000 then "Large"
else if [Amount] >= 250 then "Standard"
else "Small"
Read the expression as a decision sequence. A null amount gets No amount. Otherwise, an amount of at least 1,000 gets Large. If it did not meet that test, an amount of at least 250 gets Standard. Every remaining value gets Small. For example, 1,200 returns Large, 600 returns Standard, and 100 returns Small.
Each branch returns text in this example, which gives the new column a consistent kind of value. If you use a column name containing spaces or punctuation, reference it with the quoted field form, such as [#"Order Status"]. In a custom column expression, the table row context is supplied automatically; you do not need to write a row number or a lookup formula.
Add a nested if 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 the Power Query Editor, if the data is not already in a query.
- In the Editor, choose Add Column > Custom Column.
- Enter a name for the new column, then type the M expression in the Custom column formula box. Use the existing column names exactly as they appear in the query.
- Select OK and inspect the preview. If the expression is valid, the new column shows the result for each row; select the column’s data type control if you need to set or correct its type.
- Choose Home > Close & Load to return the result to Excel. On later refreshes, Power Query evaluates the expression against the updated source data.
The Power Query Editor is also available in Excel for Mac, though the exact ribbon layout and available connectors can vary by Excel build. The M expression itself is the same. In the Custom Column dialog, type the expression without a leading equals sign.
Order conditions from most specific to broadest
Because the first true condition wins, order is part of the logic, not just a formatting choice. Put exceptions and narrow cases before general rules. If a rule for every amount over 100 appears before a rule for amounts over 1,000, the higher-value rule will never be reached.
For example, suppose a text field named Status can contain Complete, Open, or another value. This expression gives explicit handling to the two known statuses and sends everything else to Review:
if [Status] = "Complete" then "Closed"
else if [Status] = "Open" then "In progress"
else "Review"
If you instead need to test a special status and then classify the remaining rows by amount, put the special status test first and nest the amount rules in the later branch. Before relying on text comparisons, check whether source values contain inconsistent capitalization or extra spaces. If needed, normalize the source text in an earlier step—for example with Transform > Format > lowercase or Trim—so that variations do not fall through to the final result unexpectedly.
Handle nulls, errors, and data types
A null is a missing value, not the text string "null". A condition such as [Amount] = null is a direct way to route missing amounts to a deliberate result. It is often important to put that check before numeric comparisons: a missing value is not a meaningful amount to classify, and a comparison can produce a null or an error instead of a logical true or false.
Errors are different from nulls. A value that cannot be converted to a number may already be an error, and a nested if does not automatically repair it. If the source column is supposed to be numeric, review the Changed Type step and the source values. If the requirement is to catch conversion errors, Power Query’s try ... otherwise expression can provide an explicit fallback, but use it only when treating those errors as that fallback is appropriate. Otherwise, replacing a bad value with a normal category can hide a data-quality problem.
Keep the result types of the branches compatible. Returning text from some branches and a number from others can leave a column with the broad Any type or cause problems in later steps that expect a consistent type. Decide whether the output should be text, a number, a date, or a nullable value, and return values of that type in every branch. For a numeric output, use a number such as 0 for a fallback rather than the text "0".
Use Conditional Column or write M directly?
For straightforward rules, the built-in Conditional Column dialog can create the logic without requiring you to type the full expression. In the Power Query Editor, choose Add Column > Conditional Column, select a column and comparison for each clause, enter the output values, and set the Else result. Use Add Clause to include another rule. The dialog creates a step in the query, and you can inspect its generated M in the formula bar or Advanced Editor.
- Choose Conditional Column when the rules are simple comparisons against values or thresholds and you want the dialog to build the step.
- Choose Custom Column when the condition needs a nested expression, several fields, arithmetic, text functions, or logic that is awkward to represent in the dialog.
- Use an earlier transformation when the same cleanup or derived value is needed by several later rules. A clear sequence of named steps can be easier to maintain than one very long expression.
Conditional Column is not a different calculation engine; it is a user-interface route for creating conditional M logic. A Custom Column expression gives you more direct control, but it also means you need to check syntax, field names, and branch types yourself.
Common mistakes and how to fix them
- Leaving out the final else: M requires an else branch for every if expression. Add a meaningful fallback such as a category, a null value, or a value that flags an unclassified row.
- Putting thresholds in the wrong order: A broad condition can capture rows intended for a narrower one. Reorder the tests from the most restrictive case to the broadest, then check boundary values.
- Using an Excel formula:
=IF(A2>100,"High","Low")is worksheet syntax, not a Power Query custom-column expression. In M, use a field reference such as[Amount]and theif ... then ... elseform. - Typing a column name incorrectly: Names must match the query’s column headers. For a header such as Order Status, use
[#"Order Status"], including the number sign and quoted name. - Assuming a condition handles invalid source data: A nested if determines which result to return; it does not automatically convert text to numbers or remove errors. Check column types and inspect rows showing Error in the preview.
- Returning mixed types: Make the output consistent, or deliberately use null where a value is absent. Then set the new column’s data type so subsequent calculations and loads behave as expected.
When testing thresholds, check values just below, exactly at, and just above each boundary. For a rule that uses >= 250, the value 250 belongs in that branch, while 249.99 does not. This small check catches many ordering and operator mistakes before the query is refreshed on a larger dataset.
When another approach is easier to maintain
A nested if is a good fit for a short, fixed set of rules. If there are many categories, the expression can become difficult to read and update. Consider creating a small mapping table and merging it into the query when values need to be maintained by other people or changed frequently. For numeric bands, a reference table can also make the band limits and labels visible as data rather than burying them in a long expression.
Nested conditions can also be split across multiple named steps. For instance, one step might identify missing values, another might create a broad category, and a later step might refine that category. This can make debugging easier because the preview shows intermediate results. The trade-off is a longer list of steps, so use names that explain each transformation.
Unlike a worksheet IF formula, a Power Query conditional runs as part of the query’s transformation process. It produces values when the query is evaluated or refreshed; it is not a live formula in each loaded worksheet cell. If you need cell-by-cell recalculation based on edits made directly in the output sheet, a worksheet formula may be the more suitable tool.
Frequently Asked Questions
Can Power Query have more than two if conditions?
Yes. Add another else if condition then result branch for each additional test, followed by one final else. Put the tests in the order that should take priority.
Does a nested if need parentheses?
Usually not when written in the standard chained form, with the next if in the preceding else branch. Line breaks and indentation are optional but make longer expressions easier to review.
Can I use AND or OR in a condition?
Yes. M supports logical operators such as and and or. For example, a condition can test whether a numeric value is at least 250 and below 1,000. Make sure each side evaluates to a logical value and handle nulls where appropriate.
Why does my nested if show an error?
Common causes include a missing then or else, a misspelled field name, a condition that does not return true or false, an unexpected source type, or an existing error in the row. Check the expression and select an Error cell in the preview to inspect its details.
Is Power Query if the same as Excel IF?
No. Excel’s IF is a worksheet function entered in a cell. Power Query uses M expressions with lowercase if, then, and else, and the result is created during query evaluation or refresh.
Related
- Power Query String Length: How to Count Characters
- Excel Goal Seek: How It Works and When to Use It
- Conditional Formatting for Duplicates in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Overdue Days: Dates and ExamplesTo calculate overdue days in Excel, put the due date in A2 and use =IF(A2="","",MAX(0,TODAY()-A2)) in another cell.…
- Excel ROUND: Does It Round Positive or Negative Numbers?Excel’s ROUND function rounds both positive and negative numbers to the nearest value at the specified decimal place.…
- Python pandas Excel Functions: Read and Write FilesPython pandas handles Excel files mainly with read_excel() to load workbook data into a DataFrame and DataFrame.to_excel() to…
- AVERAGE vs AVERAGEIF in Excel: Which Should You Use?Use AVERAGE to find the mean of numeric values in a range; use AVERAGEIF to average only values…
- How to Count Unique Values in a Row in Excel EasilyTo count distinct, nonblank values across a row in Microsoft 365, use =IFERROR(COLUMNS(UNIQUE(FILTER(B2:K2,B2:K2<>""))),0). This assumes the values are…
- VLOOKUP in Excel: Steps, Examples, and Common FixesTo use VLOOKUP in Excel, put the value you want to find in a cell, then enter a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.