In Excel Power Query, an IF expression uses M language syntax: if condition then value_if_true else value_if_false. Add it as a custom column in Power Query Editor, or use the Conditional Column command to build a basic rule without writing the expression yourself.
What an IF expression does in Power Query
Power Query evaluates an IF expression for each row of a table and returns one value for that row. For example, a sales table could label each order as either High or Standard depending on its amount. The result can be a new column, or an expression used as part of a larger transformation.
Power Query formulas are written in M, a language for describing data transformations. That makes them different from worksheet formulas such as Excel’s IF() function. In a worksheet, you might refer to a cell such as A2; in a Power Query custom column, you usually refer to the current row’s field as [Amount].
The words if, then, and else are required, and they are written in lowercase. An IF expression must include an else result: Power Query needs to know what to return when the condition is false. The expression returns a value; it does not change the source cell or act as a worksheet formula.
Basic syntax and a first example
Suppose a query contains a numeric column named Amount and you want to label amounts of 1,000 or more as Large. In Power Query Editor, choose Add Column > Custom Column, enter a name such as Amount Category, and use this expression:
if [Amount] >= 1000 then "Large" else "Small"
This assumes Amount is a number for every row. A value of 1,250 returns Large; a value of 800 returns Small. The square brackets identify a column in the current row. The comparison produces a true-or-false condition, and the expression after then or else supplies the output.
To create the column on Excel for Microsoft 365 for Windows:
- Select a cell in the source data and choose Data > From Table/Range. If the data is not already an Excel table, Excel will prompt you to create one.
- In Power Query Editor, choose Add Column > Custom Column.
- Enter a new column name and the M expression, then select OK.
- Review the preview and the new column’s data type. When ready, choose Home > Close & Load to return the result to Excel.
Column names with spaces are still written inside brackets, as in [Order Amount]. If a column name has punctuation or other characters that require special handling, Power Query may show a quoted identifier such as [#"Order Amount"] in generated M. Selecting the field from the editor’s available-column list helps avoid spelling mistakes.
Use Conditional Column instead of typing M
For a straightforward rule, the Conditional Column dialog can create the logic for you. In Power Query Editor, choose Add Column > Conditional Column, give the result a name, select the column and comparison, and enter the value to test. Then specify the output for matching rows and the output for the else case. You can add more clauses for additional outcomes.
This is useful when a rule has only a few conditions and you would rather configure it through controls than write code. Power Query generates the M step behind the dialog, so the result is still part of the query and will run again when the query refreshes. For complicated logic, Custom Column is often easier to read and maintain, especially when you need to combine tests or handle missing values explicitly.
Conditional Column is not the same as Excel’s worksheet Conditional Formatting. Conditional Formatting changes how cells look; a Power Query conditional column creates data in the query result, such as a text label, number, or date. It also does not make a permanent change to the original source file.
Multiple conditions, AND, OR, and nested IFs
Use and when all conditions must be true, and or when at least one condition can be true. For example, suppose a table has numeric columns named RegionCode and Sales, and a promotion applies when RegionCode is 10 and Sales is at least 500. A custom column could use:
if [RegionCode] = 10 and [Sales] >= 500 then "Eligible" else "Not eligible"
If either of two regions qualifies, use or between the region tests. Add parentheses when they make a longer condition easier to understand, for example ([RegionCode] = 10 or [RegionCode] = 20) and [Sales] >= 500. Parentheses clarify which checks belong together; they are especially helpful when mixing and with or.
For several distinct outcomes, put another IF expression in an else branch. With numeric Sales and a target of 1,000, a three-level label can be written as:
if [Sales] >= 1000 then "Target met" else if [Sales] >= 500 then "Partial" else "Below target"
Power Query tests the first condition before moving to the next one. Order matters: if the broadest condition comes first, it can capture rows that you meant to classify under a later condition. In this example, the 1,000 threshold comes first, followed by 500; a sale of 750 is Partial, while 1,200 is Target met.
Handle nulls and data types deliberately
A null represents a missing or unknown value; it is not the same as zero, an empty text string, or the text value “null”. If a column can contain missing amounts, make that case explicit before applying a numeric rule. Assuming Amount is numeric or null, this expression returns a label for each case:
if [Amount] = null then "Missing" else if [Amount] >= 1000 then "Large" else "Small"
Checking for null first makes the intended outcome clear and prevents a missing amount from being treated as an ordinary small sale. If source data sometimes stores numbers as text, comparisons can instead fail with a type error. For example, the text value “1200” is not automatically a reliable substitute for the number 1200 in every operation.
Before writing the condition, inspect the type icon in the column header and use the appropriate transformation if needed. In Power Query Editor, select the column and choose Transform > Data Type to set a suitable type. You can also convert values as part of the query, but check how invalid entries should be handled rather than silently assigning them a category. A query’s automatic type-detection step may not match the rules your data requires.
Common errors and how to fix them
Many IF-expression problems come from syntax, column references, or unexpected data rather than the condition itself. If Power Query marks an expression as invalid or shows Error in the result, check these points:
- Missing else: Every IF expression needs an else branch, even when you think the condition will always be true.
- Wrong capitalization: Write the M keywords as
if,then, andelse. M is case-sensitive, so also match column names and text values as they appear in the data. - Wrong type: Confirm that values being compared are actually numbers, dates, or text as appropriate. A number stored as text can produce an error or an unexpected comparison.
- Misspelled field: Use the exact column name, including spaces. If a previous step renamed or removed the column, update the expression or choose a step where the field still exists.
- Unexpected nulls or errors: Decide what missing values and existing error values should mean. A null check does not automatically repair errors already present in a column.
- Text matching: Check spelling, spaces, and capitalization in text comparisons. If case-insensitive matching is required, normalize the value first, for example by comparing
Text.Upper([Status])with an uppercase text value.
To investigate, select a row with an error and read the message shown in the preview or error details. The Applied Steps pane helps identify which transformation introduced it. If a rule is difficult to debug, temporarily create a simpler custom column that returns a basic label, verify the field and type, and then add complexity one condition at a time.
Power Query IF versus Excel IF
Excel’s worksheet IF() function and Power Query’s if ... then ... else expression solve related problems, but they run in different places and use different syntax. A worksheet formula calculates in a cell and can update as cell values change. A Power Query expression is a step in a data-import or transformation process and is evaluated when that query runs or refreshes.
For a worksheet, an IF formula might look like =IF(A2>=1000,"Large","Small"). In Power Query, the equivalent idea refers to the current row’s field and uses M syntax: if [Amount] >= 1000 then "Large" else "Small". Do not paste an M expression into a worksheet cell, or a worksheet formula into the Custom Column box; each environment expects its own language.
Use Power Query when the rule belongs to a repeatable import or cleanup process, such as categorizing every row in a refreshed sales file. Use a worksheet formula when the calculation needs to sit beside the data and respond immediately to worksheet edits. You can also load query results to Excel and add worksheet formulas afterward, but remember that a refresh may replace or resize the loaded result, so plan where those formulas and output columns belong.
Refresh behavior and keeping rules maintainable
A custom column is saved as part of the query’s Applied Steps. When the source changes and you refresh the query, Power Query applies the same rule to the incoming rows. That is useful for recurring reports, but it also means the rule depends on the source keeping compatible column names and data types. A renamed source field can break a later step even when the IF logic itself is correct.
Give the new column a descriptive name and keep conditions in a readable order. If several steps depend on the same complicated category rule, consider whether the logic belongs in a separate query or should be documented in a step name. A sequence of clear transformations is generally easier to review than one very long nested expression. Before relying on a refreshed result, check a few known examples, including values at the exact threshold, values just above and below it, and missing or unusual entries.
Power Query Editor features and availability can vary by Excel platform and build. The steps above describe Excel for Microsoft 365 on Windows. Excel for Mac includes Power Query capabilities, but editor options and available connectors can differ; Excel for the web is not a full substitute for authoring every desktop query. If a menu or connector is absent, check the capabilities of the specific Excel version and platform rather than assuming the M expression has changed.
Frequently Asked Questions
Does Power Query have an IF function?
It has an IF expression rather than Excel’s IF() worksheet function. Write it as if condition then result_if_true else result_if_false.
How do I write IF with multiple conditions in Power Query?
Combine tests with and when all must be true or or when any can be true. For more than two possible outcomes, use a nested IF in an else branch or add clauses with Conditional Column.
Can I use IF to return a blank?
Yes. In M, use null for a missing value, as in if [Amount] >= 1000 then "Large" else null. This is a null result, not an empty text string.
Why does my Power Query IF expression show an error?
Check for a missing else, incorrect field name, mismatched data type, unexpected null, or an error already present in the source column. Review the error details and the Applied Steps pane to locate the failing step.
Will a Power Query IF rule update when the source data changes?
It runs again when the query is refreshed, applying the saved rule to the data returned at refresh time. It does not recalculate continuously like a worksheet formula when you edit a source cell.
Related
- How to Record an Entire PowerPoint Presentation
- Online Budget Spreadsheet Free? Options, Costs and Setup
- Office Inventory Spreadsheet: Setup, Tracking, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUBTOTAL Formula Shortcut: Insert It Quickly in ExcelIn Excel for Microsoft 365 on Windows, press Alt+= to run AutoSum, but this is not a dedicated…
- Excel Mortgage Formula PMT: How to Use ItThe Excel PMT function calculates the periodic payment for a loan based on a constant interest rate and…
- Excel Percentage Change Formula: Examples and Common ErrorsTo calculate percentage change in Excel, subtract the old value from the new value, divide by the old…
- Excel Table Array Lookup: Using VLOOKUP in a TableAn Excel table array lookup usually means using the table_array argument in VLOOKUP: it tells Excel which range…
- Excel Sum Across Sheets: Formulas and 3D ReferencesTo sum the same cell across consecutive worksheets in Excel, use a 3D reference such as =SUM(Jan:Mar!B2). If…
- How to Add a New Line in Excel CONCATENATETo add a new line between text values joined with Excel’s CONCATENATE function, insert CHAR(10) between the cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.