In Excel Power Query, create an if/then/else condition in a custom column with an M expression such as if [Quantity] > 0 then "In stock" else "Out of stock". In Excel for Microsoft 365 on Windows, open the query, choose Add Column > Custom Column, and enter the expression without a leading equals sign.
What IF means in a Power Query custom column
Power Query uses the M language, not the Excel worksheet formula language. Its conditional structure is if condition then value_if_true else value_if_false. The condition must evaluate to true or false, and both result branches must be present. For multiple outcomes, M supports an else if branch before the final else.
This distinction matters because a worksheet formula such as =IF(A2>0,"In stock","Out of stock") is not the expression to paste into the Custom Column box. The Power Query equivalent refers to a field by its column name in square brackets, like [Quantity], and does not start with =. Power Query evaluates the expression for each row and creates a new column in the query result; it does not put a formula into worksheet cells.
Power Query expressions are written in M, whose keywords and text values are case-sensitive in practice: use the keyword if, not Excel’s IF function. The editor usually supplies surrounding query code for you. In the custom-column dialog, enter the expression itself, not a complete query or a worksheet formula.
Where to enter the expression
In Excel for Microsoft 365 on Windows, open the query in Power Query Editor. You can select a cell in the source table and use Data > From Table/Range to start a query, or open an existing query through Data > Queries & Connections and double-click it. In the editor:
- Choose Add Column > Custom Column.
- Enter a descriptive name in New column name.
- Type the M expression in Custom column formula. Select a listed column and use Insert if you want the editor to insert its reference.
- Check the formula for errors, then choose OK.
- Review the preview and set the new column’s data type if needed. Choose Home > Close & Load to return the result to Excel.
The formula box accepts M expressions such as if [Quantity] > 0 then "In stock" else "Out of stock". The formula bar or Advanced Editor shows more of the query’s M code, including the step that adds the column. That step typically uses Table.AddColumn and applies the expression to each row. You generally do not need to write that surrounding code when using the dialog.
Power Query is also available in Microsoft 365 for Mac, but data-source support and editor details can vary by platform and build. If a command or connector is missing, check Excel’s current Power Query support for Mac; the M conditional syntax itself is the same.
Basic conditions and multiple outcomes
Use a comparison operator to define the test. Common M comparisons include =, <> (not equal), >, <, >=, and <=. The equals sign inside an M condition compares values; it is not the leading equals sign used to begin a worksheet formula.
Assuming the source has a numeric Score column, this expression assigns a grade in descending threshold order:
if [Score] >= 90 then "A" else if [Score] >= 80 then "B" else if [Score] >= 70 then "C" else "Needs review"
The first true condition wins. A score of 85 returns B, while 68 returns Needs review. Put the most restrictive or highest threshold first; if the first test were [Score] >= 70, a score of 95 would match it and never reach the A test.
For two conditions that must both be true, use and; use or when either condition can qualify. Assuming columns named Region and Sales, an expression can flag high sales in one region:
if [Region] = "West" and [Sales] >= 1000 then "Review" else "Standard"
Use parentheses when they make a combined condition easier to read. If conditions grow into many branches, consider whether a lookup or a separate mapping table would be easier to maintain than a long nested expression.
Handle nulls and missing values deliberately
A Power Query null represents a missing or unknown value. It is not the text "null", an empty string, or the number zero. A comparison involving null may not produce the true-or-false result that an if condition needs, so test for null explicitly when a column can be blank.
Assuming Discount is a numeric source column and you want to distinguish missing discounts from values below 10, use:
if [Discount] = null then "Missing" else if [Discount] < 10 then "Low" else "Regular"
The null test comes first, so the next comparison is only evaluated for a non-null value. This helps avoid errors and makes the intended treatment visible. If the data can contain both nulls and empty text, test for each representation that actually occurs; an empty string is not automatically the same as null.
For text columns that may be missing, a direct null branch can return a label, or it can return null to preserve missingness. Decide based on how the output will be used. Replacing every null with zero or an empty string may make a later calculation or summary misleading.
Text, date, and type considerations
Conditional expressions must compare compatible values. A number stored as text is not necessarily interchangeable with a numeric value, and a date column should not be compared as though it were ordinary text. Inspect the column’s type icon in Power Query and use the appropriate type-conversion step before building the condition when the source type is wrong.
Assuming a text column named OrderStatus, this expression groups two text values together:
if [OrderStatus] = "Shipped" or [OrderStatus] = "Delivered" then "Complete" else "In progress"
It checks exact values. It does not automatically ignore extra spaces or differences in spelling, so values such as "Shipped " may need to be cleaned first. For more complex text rules, M provides text functions, but use them only after confirming that the input is text and deciding how nulls should be handled.
Assuming OrderDate has the date type and ShipDate is either a date or null, a basic delay flag is:
if [ShipDate] = null then "Not shipped" else if [ShipDate] > [OrderDate] then "Shipped later" else "Same day or earlier"
Power Query can infer a type for a custom column, but inference is not a substitute for checking it. A column that returns text labels in every branch should be text; a calculated amount should remain numeric if you intend to aggregate it. Select the new column and use Transform > Data Type to set the appropriate type. Consistent result types across branches make downstream steps more predictable.
Common errors and how to fix them
If the editor reports a syntax error, check that the expression has all three parts: if, then, and else. M uses double quotation marks around text literals, and parentheses or brackets must be balanced. A missing final else is a frequent cause of an invalid expression.
- Unknown field: Verify the column name and spelling. References must match the current query column, including spaces and punctuation. Insert the field from the dialog’s column list to reduce typing mistakes.
- Operator mismatch: Check that both sides have compatible types. Convert a text number to a number, or a text date to a date, before comparing if appropriate.
- Error on blank rows: Add an explicit null branch before performing a comparison or text operation that expects a value.
- Unexpected category: Check the order of nested conditions and make sure the final else represents the intended fallback.
- Correct preview, wrong loaded result: Confirm that the query was refreshed and that later applied steps have not changed or removed the new column.
When a row displays Error, select the error cell to inspect its details. The message often points to a type conversion, missing field, or invalid operation rather than to the conditional structure itself. Also check earlier steps: a renamed or removed column can make a later custom-column expression invalid.
Custom column versus conditional column
Custom Column and Conditional Column are two ways to add a conditional result. Choose Add Column > Conditional Column when the rule is a straightforward set of field comparisons and you prefer a guided interface. The dialog builds the logic for you and can be easier to inspect for simple categories.
Choose Add Column > Custom Column when you need to write M directly, combine conditions, use functions, or create a rule that the guided dialog cannot express conveniently. Both approaches add a query step and refresh with the query; neither creates a live worksheet formula in each row. If a rule changes often or is maintained by several people, a separate table of thresholds or category mappings may be clearer than editing a long conditional expression.
Frequently Asked Questions
Do I type an equals sign before an IF expression in Power Query?
No. In the Custom Column formula box, enter an M expression beginning with if. The leading equals sign belongs to worksheet formulas, not the expression entered in this dialog.
Can I use Excel’s IF function in a Power Query custom column?
No. Power Query uses M syntax: if condition then result else alternate. It does not use Excel’s IF(condition, value_if_true, value_if_false) function syntax.
Can a custom column have more than two outcomes?
Yes. Add one or more else if tests and finish with a final else. Arrange the tests so that a value is not caught by a broad condition before reaching a more specific one.
How do I return a blank when a condition is false?
Assuming the output should remain missing rather than contain empty text, use null as the final result, for example if [Flag] = "Yes" then "Keep" else null. If you specifically want an empty text value, use ""; the two results are different.
Does a Power Query custom column update when the source changes?
It is recalculated when the query refreshes, using the current source data and the saved query steps. It does not recalculate instantly like a worksheet formula after every cell edit. Refresh the query to retrieve and transform updated data.
Related
- How to Use Slide Master View in PowerPoint Effectively
- Project Tracker Spreadsheet: Free Options and Setup
- Project Tracker Spreadsheet Benefits and Trade-Offs
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Text Join in Excel With a Comma: Formula and ExamplesTo join the values in cells A2:A10 with a comma and a space, use =TEXTJOIN(", ",TRUE,A2:A10). The formula…
- Excel Table Filter Multiple Values: Methods That WorkTo filter an Excel table for several values, open a column’s filter arrow, clear Select All, and check…
- Excel Protected Sheet Sort: Settings, Limits, and FixesTo sort a protected Excel worksheet, choose Review > Protect Sheet, allow users to sort, and protect the…
- How to Forecast Dates in Excel with Useful FormulasTo extend a regular date schedule, add the interval to the previous date—for example, with a date in…
- How to Find Duplicate Entries in Excel with FormulasTo find duplicate entries in Excel, select the cells you want to check and choose Home > Conditional…
- XLOOKUP in Excel: Find and Return Data From Another SheetYes. XLOOKUP can search a range on one worksheet and return the matching value from another range on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.