Power QueryExercises

Mapping tables vs nested if: categorise values the maintainable way

Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

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
    Visible

Result

TicketCodeChannelTeam
T-1EMEmailSupport
T-2PHPhoneSupport
T-3WAUnmapped codenull
T-4EMEmailSupport

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

WA is new. It's labelled Unmapped code instead of being lumped into a default category, so someone can add it to the table.

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

CodeDay
TTuesday
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
    Tiered

Result

OrderIDQtyTier
A-12Small
A-23Medium
A-36Bulk
A-411Bulk

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

Bands must be sorted by From. The (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.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

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.