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/
ecoromka
9 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.