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
v-diye-msft's avatar
v-diye-msft
Community Support
6 years ago

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

    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),