Power QueryExercises

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
    Tiered

Result

OrderIDQtyTier
A-11Small
A-22Small
A-33Medium
A-45Medium
A-56Bulk
A-6nullUnknown

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The test rows sit exactly on the boundaries (2, 3, 5, 6), which is how you should test a banding rule.

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 and across two columns); switch to a custom column when it gets awkward.
  • The simulator evaluates if exactly as M does: a null or non-logical condition is an error.
Functions in this lesson:Table.AddColumn

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.