Optimizing multiple replacements

[ecoromka]

Hello all!

I'm facing a problem, when trying to normalize text data. Source contains diacritics (additional symbols, based on latin, like Õ).

In order to normalize it, i can sequently invoke Table.ReplaceValue with Replacer.ReplaceText parameter. So there would be about 60 steps. But it seems very unelegant. It seems there should be a way to get replace rules from separate table and loop through them.

Was trying to utilize 'each' function or recursive definition with '@', but my skill in M seems to be not enough to get all of it together.

The task seems to be quite usual, can anyone say, how it's done in Power Query?

[augusto_flytour]

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.

[ImkeF · Accepted solution]

Have a look if this works for you:

http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/

[ecoromka]

Thank you! That helped.

I rewrote code a bit, to replace not with Text.Replace, but with Table.ReplaceValue.

Here is the result (all steps shown):

let
    SourceTable = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(SourceTable,{{"First name", type text}, {"Middle Name", type text}, {"Last Name", type text}, {"personal title", type text}, {"generation", type any}, {"First Name (latin)", type text}, {"Last Name (latin)", type text}, {"Birthday", type date}, {"Hire date", type date}, {"Title", type text}, {"Company", type text}, {"Department", type text}, {"Language preferences", type text}, {"Email", type text}, {"Phone", Int64.Type}, {"Cell phone", Int64.Type}, {"Office location", type text}, {"Office", type any}, {"Manager", type text}, {"Dotted-line manager", type text}, {"Employee Number", Int64.Type}, {"Employee ID", Int64.Type}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"First name", Text.Proper}, {"Middle Name", Text.Proper}, {"Last Name", Text.Proper}, {"personal title", Text.Proper}, {"First Name (latin)", Text.Proper}, {"Last Name (latin)", Text.Proper}, {"Dotted-line manager", Text.Proper}}),
    #"Lowercased Text" = Table.TransformColumns(#"Capitalized Each Word",{{"Email", Text.Lower}}),

    //Get table of word replacements
    Replacements = Excel.CurrentWorkbook(){[Name="Replacements"]}[Content],

    //Get list of strings to replace
    ReplaceWhat = List.Buffer(Replacements[Column1]),

    //Get list of strings to replace with
    ReplaceWith = List.Buffer(Replacements[Column2]),
    
    //A non-recursive function to do the replacements
    ReplaceByList = (Input, Columns)=> 
       //Use List.Generate() to do the replacements
       List.Last(List.Generate(
          ()=> [Counter=0, ProcessedData=Input], 
          each [Counter]<=List.Count(ReplaceWith), 
          each [Counter=[Counter]+1, 
                ProcessedData=Table.ReplaceValue(
                                                 [ProcessedData], 
                                                 ReplaceWhat{[Counter]}, 
                                                 ReplaceWith{[Counter]},
                                                 Replacer.ReplaceText,
                                                 Columns)], 
          each [ProcessedData])),
 
    //Add a calculated column to call the function on every row in the table
    //containing the text to change
    #"Replaced Diacritics" = ReplaceByList(#"Lowercased Text",{"First Name (latin)","Last Name (latin)"})
in
    #"Replaced Diacritics"

Not sure this is the best way, but works reasonably fast on my limited data set.  Don't like the thing with counter and removed explicit subfunction definitions.