Replace Multiple Values Using a Mapping Table in Power Query M

Replaces many old values with new ones in one pass by joining against a mapping table instead of chaining Table.ReplaceValue.

Data CleaningIntermediatemapping tablereplacelookupmerge

Power Query M code

let
    Source = YourTable,
    Mapping = MappingTable,
    Merged = Table.NestedJoin(Source, {"ColumnName"}, Mapping, {"OldValue"}, "MapMatch", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "MapMatch", {"NewValue"}),
    FinalColumn = Table.AddColumn(Expanded, "ColumnNameClean", each if [NewValue] <> null then [NewValue] else [ColumnName], type text)
in
    FinalColumn

Notes

MappingTable needs one row per old/new pair with OldValue and NewValue columns; rows with no match keep their original value via ColumnNameClean.

Requirements

How to use this snippet

  1. 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).
  2. Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
  3. 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 Replace Multiple Values Using a Mapping Table.