Skip to content

Instantly share code, notes, and snippets.

@hohlick
Created February 20, 2018 16:06
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save hohlick/a7ab8342659d1083e2815572562171cb to your computer and use it in GitHub Desktop.
Save hohlick/a7ab8342659d1083e2815572562171cb to your computer and use it in GitHub Desktop.
Power Query / Power BI / M function for mass values replacement in a table column (List version)
// fnMassReplace
(
Source as table, // table to make replacements in
ToReplace as text, // name of the column for replacements
ReplaceWhat as list, // list (or column reference) with "what to replace" values
ReplaceWith as list // list (or column reference) with "replace with" values
) as table =>
let
CurrentColumns = List.Buffer(Table.ColumnNames(Source)),
CurrentType = Value.Type(Source),
MatchReplace = List.ReplaceMatchingItems(Table.Column(Source,ToReplace), List.Zip({ReplaceWhat, ReplaceWith})),
NewTab = Table.RemoveColumns(Source, {ToReplace}),
NewColumnsList = List.RemoveMatchingItems(CurrentColumns, {ToReplace}),
AddColumn = Table.ToColumns(NewTab) & {MatchReplace},
Collected = Table.FromColumns(AddColumn, NewColumnsList & {ToReplace}),
Ordered = Table.ReorderColumns(Collected, CurrentColumns),
Typed = Value.ReplaceType(Ordered, CurrentType)
in
Typed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment