Forum Discussion

charleshale's avatar
charleshale
Continued Contributor
6 years ago

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 ...
Need Help
charleshale's avatar
charleshale
Continued Contributor
6 years ago

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"