Forum Discussion
Optimizing multiple replacements
- 9 years ago
Have a look if this works for you:
http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/
Hello! Just wanted to share my solution. Basically I created a function (broken in 3 steps) to perform the replacements in multiple columns:
In Power Query:
1. Create a new blank query and name it AccentedCharsList
2. In Advanced Editor:
let
Source = #A table with 2 columns. Column1 has the characters to be replaced and Column2 has the corresponding replacement#, Result = List.Buffer(Table.ToRows(Source)) in Result
3. Create a new blank query and name it ReplaceAccentedChars
4. In Advanced Editor:
(InputText as text) =>
let
TextChars = Text.ToList(InputText),
Replaced = List.ReplaceMatchingItems(TextChars, AccentedCharsList),
Result = Text.Combine(Replaced)
in
Result
5. Create a new blank query and name it ReplaceAccents
6. In Advanced Editor:
(Source as table, Columns as list) =>
let
OpList = List.Repeat ({ReplaceAccentedChars}, List.Count (Columns)),
TypeList = List.Repeat ({type text}, List.Count (Columns)),
TransList = List.Zip ({Columns, OpList, TypeList}),
Result = Table.TransformColumns (Table.Buffer(Source), TransList)
in
Result
USAGE
ReplaceAccents(SourceTableName, {"Column1", "Column2",...})Substitute SourceTableName with the actual table you are working on, as well as the column names.
You can place as many columns as you want to perform the replacements.
Values in the Columns must be of the type text, otherwise an Error will be returned in that field.
I decided to create the function in 3 steps because I may use the intermediate function ReplaceAccentedChars in single fields, not on entire columns.
Hope to have helped someone else.