Created
February 20, 2018 16:06
-
-
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)
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
// 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