-
-
Save ImkeF/3bf0aa9c61849ea1db24f85442ed287c to your computer and use it in GitHub Desktop.
Replaces multiple values at a time in a table column without recursion.
This file contains 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
let func = | |
( | |
TableWithTextColumn as table, | |
ColumnNameTextToReplace as text, | |
ReplacementsTable as table, | |
ColumnNameWordToReplace as text, | |
ColumnNameReplaceWith as text | |
) => | |
let | |
Source = ReplacementsTable, | |
CreateListOfLists = Table.AddColumn( | |
Source, | |
"Liste", | |
each ({Record.Field(_, ColumnNameWordToReplace), Record.Field(_, ColumnNameReplaceWith)}) | |
), | |
Text = TableWithTextColumn, | |
TurnTextToList = Table.AddColumn( | |
Text, | |
"Custom", | |
each Text.Split(Record.Field(_, ColumnNameTextToReplace), " ") | |
), | |
Replacements = Table.AddColumn( | |
TurnTextToList, | |
"Changed Text Expected", | |
each Text.Combine(List.ReplaceMatchingItems([Custom], CreateListOfLists[Liste]), " ") | |
), | |
Cleanup = Table.RemoveColumns(Replacements, {"Custom"}) | |
in | |
Cleanup , | |
documentation = [ | |
Documentation.Name = " Table.ReplaceMultiple.pq ", | |
Documentation.Description = " Replaces multiple values at a time in a table column without recursion. ", | |
Documentation.LongDescription = " Replaces multiple values at a time in a table column without recursion: http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ The <code>ReplacementsTable </code> must have the values to be replaced in the 1st column and the new value in the 2nd. ", | |
Documentation.Category = " Table.ColumnOperations ", | |
Documentation.Source = " http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ . ", | |
Documentation.Version = " 2.0 ", | |
Documentation.Author = " Imke Feldmann ", | |
Documentation.Examples = {[Description = " ", | |
Code = " let | |
TableWithText = #table( {""Text""}, List.Zip( { {""the cat sat on the mat"" ,""the cat sat next to the dog"" ,""the dog chased the cat"" ,""the dog sat on the mat"" ,""the catamaran sails through the air""} } ) ), | |
ReplacementsTable = #table( {""Word To Replace"", ""Replace With""}, List.Zip( { {""cat"" ,""mat"" ,""dog"" ,""the"" ,""air""}, {""bear"" ,""chair"" ,""dragon"" ,""THE"" ,""water""} } ) ), | |
// The query containing the function has to be called ""fnTable_ReplaceMultiple_pq"" | |
Result = fnTable_ReplaceMultiple_pq ( TableWithText , ""Text"", ReplacementsTable, ""Word To Replace"", ""Replace With"" ) | |
in | |
Result ", | |
Result = " Changed Text Expected | |
--------------------- | |
THE bear sat on THE chair | |
THE bear sat next to THE dragon | |
THE dragon chased THE bear | |
THE dragon sat on THE chair | |
THE catamaran sails through THE water "]}] | |
in | |
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
It seems the code at Line 27 is hardcoded to "Text" column name. I modified the last few lines of the m code to handle this:
// Had to add/modify the next two lines to use ColumnNameTextToReplace instead of hardcoded "Text" column name
#"Removed Columns" = Table.RemoveColumns(Replacements,{ColumnNameTextToReplace, "Custom"}),
Cleanup = Table.RenameColumns(#"Removed Columns",{{"Changed Text Expected", ColumnNameTextToReplace}})
in
Cleanup ,