M Query Text.Contains Text.Replace Comparer.Ordinalcase

[charleshale]

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

Here's the code I use (to remove suffixes like Inc. Inc a/s Corp GmbH etc)

= Table.TransformColumns(#"//PRIORCOMMAND//", {{"Col2Clean", each if 	
	Text.EndsWith(_,"." , Comparer.OrdinalIgnoreCase) or
	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(_," de" , Comparer.OrdinalIgnoreCase) or
	Text.EndsWith(_," nv" , Comparer.OrdinalIgnoreCase) or
	Text.EndsWith(_," oü" , Comparer.OrdinalIgnoreCase) or
	Text.EndsWith(_," oy" , Comparer.OrdinalIgnoreCase) or
	Text.EndsWith(_," ry" , Comparer.OrdinalIgnoreCase) or
	Text.EndsWith(_," se" , Comparer.OrdinalIgnoreCase) or
then Text.Start(_,Text.Length(_)-3) else _, type text}})	

And then I will have another similar command for 4 space suffixes like " inc".    This works well and is much more processor friendly for me than either Table.ReplaceValue or the worshipful master @ImkeF 's bulk word find replace.

SO - here's my question.   Let's say that I want to replace strings not at the end or beginning of a string.  I am struggling to engineer super simple and processor-effective code using text.contains to do this with ignoring case.   I'm trying something like the following but it doesnt capitalize on ignore case

= Table.TransformColumns(//PRIORCOMMAND//, {{"Col2Clean", each if
          Text.Contains(_,"Finand" , Comparer.OrdinalIgnoreCase )  then 
          Text.Replace(_,"Finland", "text to replace Finland") 
else _, type text}})

Any suggestions?    Ideally this would work at the character level instead of the work level.

Thank you

Charlie

[Rickmaurinus]

Hi,

I'm a little late to the party, but thought to put this code out here for anyone who can use it. 

You can turn your logic upside down for easier syntax. Instead of using Text.EndsWith and providing a long list of OR statements, you can use List.Contains with your values to search for, and check it against your main column. 

= if 
List.Contains( {"ab", "ag", "as", "bv", "i", "kg", "ky"}, 
                Text.End( [FullReplace1], 3 ),  
                Comparer.OrdinalIgnoreCase ) 
then Text.Start( [FullReplace1], Text.Length( [FullReplace1] ) - 5) else [FullReplace1] )

Hope that helps!

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[Anonymous]

Comparer.OrdinalIgnoreCase was very useful to compare text in any form (case sensitive). Previosuly I was doing trim, then upper case and finally did the comparison which was taking time. But Comparer.OrdinalIgnoreCase was very useful.

[v-diye-msft]

Hi @charleshale 

Please kindlt check whether below helps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKTklNU4rViVYCs9LBTDArA8wEszKVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
txt = [Text] ,
list = 
List.Select(
    List.Transform({"ef","gh","fg"},Text.From),
    each Text.EndsWith(txt,_)
),
val = list{0},
len = Text.Length(val)
in 
if List.Count(list)>0
then Text.RemoveRange([Text],Text.Length([Text])-len,len)
else [Text])
in
    #"Added Custom"

Pbix attached.

[charleshale]

Oh wow. @v-diye-msft this looks super fast. Will test it out and report back and then accept solution assuming I can replicate. Thank you. What is the significance of the following hash in Binary.Decompress(Binary.FromText ?

("i45WSkxKTklNU4rViVYCs9LBTDArA8wEszKVYmMB", BinaryEncoding.Base64),

[parry2k]

@charleshale so the code you have is not working or you are getting any error?

[charleshale]

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? 

[amitchandak]

@charleshale , there is discussion here. Check if that can help

https://social.technet.microsoft.com/Forums/en-US/10ee83da-c2dc-4957-9d8c-d961f669fdd4/case-insensit...

[charleshale]

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"