Forum Discussion
ecoromka
9 years agoNew Member
Optimizing multiple replacements
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 Tab...
- 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/
ImkeF
9 years agoCommunity Champion
Have a look if this works for you:
http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/
- ecoromka9 years agoNew Member
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.