Forum Discussion
Power Query - Replace multiple substrings in one column
- 8 years ago
Hi Floriankx,
Here are some blogs written about multiple replacements of words with Power Query:
Multiple replacements or translations in Power BI and Power Query
Multiple replacements of words in Power Query
Replace multiple values in a single step
Regards,
Yuliana Gu
I know this is old topic, but maybe it helps to someone...
Let's assume that you have table with replacement pairs called t_Replacements which contains Find and Replace columns:
Then you can add to your table new Custom Column and paste there this code
(Column with your text should have name Column1)
[
v_replacingTable = Table.Distinct(Table.TransformColumns(t_Replacements,{{"Find", Text.Lower, type text}}), "Find"),
v_replacingTableAsListOfLists = Table.ToRows(v_replacingTable),
v_textAsList = Text.Split([Column1], "_"),
v_replacedTextAsList = List.ReplaceMatchingItems(v_textAsList, v_replacingTableAsListOfLists, Comparer.OrdinalIgnoreCase),
v_replacedTextAsText = Text.Combine(v_replacedTextAsList, "_")
][v_replacedTextAsText]
Result:
Note:
If you want to debug this code - just delete [v_replacedTextAsText] after ] at the end of the code and then you can expand the record to see result of each step/variable.