Conditional columns with if … then … else
Write conditional logic that is correct at the boundaries.
~9 min read · beginner · Module 7: Numbers, dates and conditional columns
By Tahir Nas · Examples run in our simulator · Published · Updated
No prerequisites: you can start here.
A conditional column assigns a value based on tests that run top to bottom. The first test that is true wins; the rest are skipped. That makes the order of the tests part of the logic.
let
Source = #table({"OrderID", "Qty"}, {{"A-1", 1}, {"A-2", 2}, {"A-3", 3}, {"A-4", 5}, {"A-5", 6}, {"A-6", null}}),
Tiered = Table.AddColumn(Source, "Tier", each
if [Qty] = null then "Unknown"
else if [Qty] <= 2 then "Small"
else if [Qty] <= 5 then "Medium"
else "Bulk", type text)
in
TieredResult
| OrderID | Qty | Tier |
|---|---|---|
| A-1 | 1 | Small |
| A-2 | 2 | Small |
| A-3 | 3 | Medium |
| A-4 | 5 | Medium |
| A-5 | 6 | Bulk |
| A-6 | null | Unknown |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Boundaries
Write the rule down with real numbers before coding it: 1–2 Small, 3–5 Medium, 6+ Bulk. Then check each edge against your operators. Off-by-one errors with < versus <= are the most common mistake, and they're invisible unless your test data contains the boundary values.
Why the order of tests matters
If the tests were written the other way round, if [Qty] <= 5 then "Medium" else if [Qty] <= 2 then "Small", every small order would match the first test and be labelled Medium. Put the narrowest condition first, or write ranges with both ends ([Qty] >= 3 and [Qty] <= 5).
null
null <= 2 is null, and if needs true or false, so without the first test a row with a null Qty would hold an error in the new column. Test for null first. (Filters behave differently: Table.SelectRows simply drops rows whose condition is null.)
try (if null <= 2 then "Small" else "Large") otherwise "error"Result
error
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Where to find this in Excel
- Excel for Windows
- Add Column > Conditional Column
- What you see
- A dialog builds if, then and else clauses from drop-downs. The new column has no data type until you set one.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a conditional column (Microsoft Learn).
Common mistakes
- Using
<where the rule says 'up to and including', putting boundary values in the wrong band. - Writing a broad test before a narrow one so the narrow band is never reached.
- Not testing for null first, so rows with missing values hold errors.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Add Column → Conditional Column builds if/else-if chains with a dialog; Add Column → Custom Column lets you type the expression.
- The Conditional Column dialog can't express everything (for example
andacross two columns); switch to a custom column when it gets awkward. - The simulator evaluates
ifexactly as M does: a null or non-logical condition is an error.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Label orders as Small, Medium or Bulk (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- beginner~7 minPro
Label orders as Small, Medium or Bulk (kite shop)
Add a Tier column from Qty: 1–2 Small, 3–5 Medium, 6 or more Bulk.
- beginner~7 minPro
Label orders as Small, Medium or Bulk (bakery)
Add a Tier column from Qty: 1–2 Small, 3–5 Medium, 6 or more Bulk.
- beginner~7 minPro
Label orders as Small, Medium or Bulk (bike rental)
Add a Tier column from Qty: 1–2 Small, 3–5 Medium, 6 or more Bulk.