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
Rickmaurinus's avatar
Rickmaurinus
Helper V
4 years ago

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.