Mapping tables vs nested if: categorise values the maintainable way
Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated
The problem with long if chains
A 15-branch conditional column is hard to review, easy to break with a typo, and silent about new codes: whatever the final else says, every new value gets it. Rules that change belong in a table people can read.
1. Exact codes: a mapping table and a merge
let
Tickets = #table({"Ticket", "Code"}, {{"T-1", "EM"}, {"T-2", "PH"}, {"T-3", "WA"}, {"T-4", "EM"}}),
Channels = #table({"Code", "Channel", "Team"}, {{"EM", "Email", "Support"}, {"PH", "Phone", "Support"}, {"CH", "Chat", "Sales"}}),
Merged = Table.NestedJoin(Tickets, {"Code"}, Channels, {"Code"}, "C", JoinKind.LeftOuter),
Mapped = Table.ExpandTableColumn(Merged, "C", {"Channel", "Team"}),
Visible = Table.TransformColumns(Mapped, {{"Channel", each _ ?? "Unmapped code", type text}})
in
VisibleResult
| Ticket | Code | Channel | Team |
|---|---|---|---|
| T-1 | EM | Support | |
| T-2 | PH | Phone | Support |
| T-3 | WA | Unmapped code | null |
| T-4 | EM | Support |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
One mapping table can carry several attributes (Channel and Team here). In Excel, keep it as a small worksheet table; in Power BI, as an Enter Data table or a shared file.
2. A short, fixed list: a record lookup
let
Names = [M = "Monday", T = "Tuesday", W = "Wednesday"]
in
#table({"Code", "Day"}, {{"T", Record.FieldOrDefault(Names, "T", "?")}, {"F", Record.FieldOrDefault(Names, "F", "?")}})Result
| Code | Day |
|---|---|
| T | Tuesday |
| F | ? |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
3. Ranges: a band table
For thresholds, store the lower bound of each band and pick the last band whose bound is at or below the value. Changing a threshold is then an edit to one cell:
let
Bands = #table({"From", "Tier"}, {{0, "Small"}, {3, "Medium"}, {6, "Bulk"}}),
Orders = #table({"OrderID", "Qty"}, {{"A-1", 2}, {"A-2", 3}, {"A-3", 6}, {"A-4", 11}}),
Tiered = Table.AddColumn(Orders, "Tier", (o) => List.Last(Table.SelectRows(Bands, each [From] <= o[Qty])[Tier]), type text)
in
TieredResult
| OrderID | Qty | Tier |
|---|---|---|
| A-1 | 2 | Small |
| A-2 | 3 | Medium |
| A-3 | 6 | Bulk |
| A-4 | 11 | Bulk |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
(o) => names the order row so the inner each can refer to the band row.When an if chain is still fine
- Two or three branches that encode logic rather than a lookup (
if [Qty] = null then …). - Rules that combine several columns in ways a table can't express simply.
- One-off analysis that nobody will maintain.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.
Related pages
Lessons
More resources
Function reference
Sources
Microsoft's official documentation for the features and functions used on this page.
- Merge queries overview (Concept, Microsoft Learn)
- Replace values and errors (Concept, Microsoft Learn)
- Table.NestedJoin (Function reference, Microsoft Learn)
- Table.ExpandTableColumn (Function reference, Microsoft Learn)
- Table.TransformColumns (Function reference, Microsoft Learn)
- Record.FieldOrDefault (Function reference, Microsoft Learn)
- Table.AddColumn (Function reference, Microsoft Learn)
- List.Last (Function reference, Microsoft Learn)
- Table.SelectRows (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.