Created
February 20, 2018 16:10
-
-
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)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
( | |
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