Skip to content

Instantly share code, notes, and snippets.

@hohlick
Created February 20, 2018 16:10
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save hohlick/b8977cc0cf4d18fd1c9657ebe536cea6 to your computer and use it in GitHub Desktop.
Save hohlick/b8977cc0cf4d18fd1c9657ebe536cea6 to your computer and use it in GitHub Desktop.
Power Query / Power BI / M function for mass values replacement in a table column (Join version)
(
SourceTable as table, // table to make replacements in
ToReplace as text, // name of the column for replacements
ReplacementTable as table // table with columns named "What" and "With" (with "what to replace" and "replace with" values respectively)
) as table =>
let
JoinColumnName = Text.NewGuid(),
Correct = Table.NestedJoin(SourceTable, {ToReplace}, ReplacementTable, {"What"}, JoinColumnName, JoinKind.LeftAnti),
Part1 = Table.RemoveColumns(Correct, JoinColumnName),
Wrong = Table.NestedJoin(SourceTable, {ToReplace}, ReplacementTable, {"What"}, JoinColumnName, JoinKind.Inner),
RemovedColumns = Table.RemoveColumns(Wrong, {ToReplace}),
Part2 = Table.ExpandTableColumn(RemovedColumns, JoinColumnName, {"With"}, {ToReplace}),
Result = Part1 & Part2
in
Result
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment