Forum Discussion
Continued ContributorM 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
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
8 Replies
- Rickmaurinus
Helper V
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.
- parry2k
Super User
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
- v-diye-msft
Community Support
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),
