Forum Discussion
M Query Text.Contains Text.Replace Comparer.Ordinalcase
I have to do a lot of name normalization -- particularly to eliminate name suffixes in corporate names for European names. For purposes of the following these names are in a column named Col2Clean ...
charleshale so the code you have is not working or you are getting any error?
The code
= Table.TransformColumns(//PRIORCOMMAND//, {{"Col2Replace", each if Text.Contains(_,"Berlin" , Comparer.OrdinalIgnoreCase ) then Text.Replace(_,"Berlin", "something else to replace berlin") else _, type text}})
works but it fails to be optimal on 2 counts: (i) it doesnt ignore case because the Text.Replace is case sensitive, and (ii) is pretty awkward because I wouldnt need text.contains really -- just trying to use some construction that works with comparer.ordinalignorecase. Does that makes sense?
charleshale , there is discussion here. Check if that can help
Thanks, @amitchandak . I saw that. I've had a lot of processor speed problems when I bring in an external list vs hard coding it in the code, such as this:
let Source = #"fGL", //takes GL #"Removed Other Columns" = Table.SelectColumns(Source,{"Vendor", "Vendor (T)"}), //takes vendor names #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"Vendor (T)"] <> null)), #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"), #"Duplicated Column" = Table.DuplicateColumn(#"Removed Duplicates", "Vendor (T)", "Vendor (T) - Copy"), #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Vendor (T) - Copy", "Col2Clean"}}), #"FullReplace1" = Table.ReplaceValue(#"Renamed Columns" , " &" , "",Replacer.ReplaceText,{"Col2Clean"}), #"Repl11"= Table.TransformColumns(#"FullReplace1", {{"Col2Clean", each if Text.EndsWith(_,"." , Comparer.OrdinalIgnoreCase) then Text.Start(_,Text.Length(_)-1) else _, type text}}), #"Repl31"= Table.TransformColumns(#"Repl11", {{"Col2Clean", each if Text.EndsWith(_," ab" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," ag" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," as" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," bv" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," i " , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," kg" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," ky" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," nv" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," oü" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," oy" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," sp" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," ry" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," se" , Comparer.OrdinalIgnoreCase) then Text.Start(_,Text.Length(_)-3) else _, type text}}), #"Repl41"= Table.TransformColumns(#"Repl31", {{"Col2Clean", each if Text.EndsWith(_," a/s" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," asa" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," inc" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," ltd" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," oyj" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," plc" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," s.a" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," srl" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_,".net" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_,"a/s " , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_,"asa " , Comparer.OrdinalIgnoreCase) then Text.Start(_,Text.Length(_)-4) else _, type text}}), #"Repl51"= Table.TransformColumns(#"Repl41", {{"Col2Clean", each if Text.EndsWith(_," a.b." , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," s.a." , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," s.c." , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," sarl" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," sprl" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," gmbh" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_," z oo" , Comparer.OrdinalIgnoreCase) or Text.EndsWith(_,", inc" , Comparer.OrdinalIgnoreCase) then Text.Start(_,Text.Length(_)-5) else _, type text}}), #"FullReplace2" = Table.ReplaceValue(#"Repl51" , "." , "",Replacer.ReplaceText,{"Col2Clean"}), #"Removed Duplicates1" = Table.Distinct(FullReplace2, {"Vendor"}) in #"Removed Duplicates1"
