Conditional and Custom Columns
Meera asks two new questions: how much money did each order line bring in, and how many orders were large ones? The till export has neither a revenue column nor a size label. Power Query can build both — one through a dialog, one with a short formula.
The rule
Conditions are tested top to bottom and the first true one wins.
Conditional Column (Add Column → Conditional Column) opens a dialog of If … Then … rows, Else If rows and a final Else. Behind the dialog it writes an M if expression:
= Table.AddColumn(#"Changed Type", "Order Size", each
if [Qty] >= 5 then "Large"
else if [Qty] >= 3 then "Medium"
else "Small", type text)Custom Column (Add Column → Custom Column) accepts any M formula. Columns of the current row are written in square brackets:
[Qty] * [Price]— the line's revenue.[City] & " - " & [Product]— joins text with&."Qty: " & Text.From([Qty])— M never converts types silently, so a number must become text before&can join it.try Number.From([Qty]) otherwise null— attempts a conversion and falls back to null if it fails.
M's keywords are lower-case: if, then, else, and, or, not. Equality is = and inequality is <>. A custom column starts with the type Any (ABC123), so set its type straight away, or give the type as the last argument, as type text does above.
Applied to Leaf & Cup's six January orders:
| OrderID | City | Product | Qty | Price | Line Revenue | Order Size |
|---|---|---|---|---|---|---|
| 1001 | Pune | Masala Chai | 2 | 30 | 60 | Small |
| 1002 | Mumbai | Samosa | 2 | 20 | 40 | Small |
| 1003 | Pune | Green Tea | 2 | 40 | 80 | Small |
| 1004 | Mumbai | Masala Chai | 4 | 30 | 120 | Medium |
| 1005 | Pune | Samosa | 5 | 20 | 100 | Large |
| 1006 | Mumbai | Green Tea | 5 | 40 | 200 | Large |
These six orders are the ones you will model and measure for the rest of the course.
The shortcut
Order the conditions from narrowest to widest: the strictest test first, the catch-all Else last. A wide test placed first swallows the rows that a narrower test below it was written for.
Correct order
The first test is
[Qty] >= 5. 5 >= 5 is true, so the answer is "Large" and testing stops.
Drills
Answer fast — trust the rule.
With the correct Order Size formula, what is order 1004 (Qty 4)?
With the wrong order (>= 3 tested before >= 5), how many of the six orders are labelled Large?
Which custom column formula gives Line Revenue?
What does "Qty: " & [Qty] do when Qty is a whole number?
What is the combined Line Revenue of the two Large orders?
Remember
- Conditions are tested top to bottom and the first true one wins.
- Order tests from narrowest to widest, with the catch-all last.
- Traps: joining text and numbers without
Text.From, and leaving a custom column typed as Any.