Simulate a Switch Statement with a Lookup Record in Power Query M

Replaces a long if/else if chain with a lookup record, similar to a switch/case statement in other languages.

Conditional LogicIntermediateswitchcaselookuprecord

Power Query M code

let
    Source = YourTable,
    StatusMap = [Y = "Active", N = "Inactive", P = "Pending"],
    AddedStatus = Table.AddColumn(
        Source,
        "StatusLabel",
        each Record.FieldOrDefault(StatusMap, [StatusCode], "Unknown"),
        type text
    )
in
    AddedStatus

Notes

Add or remove fields in StatusMap to cover every code you expect; anything not listed falls back to "Unknown" instead of erroring.

Requirements

A table with a short code column to map to readable labels.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Simulate a Switch Statement with a Lookup Record.