Make Text.Replace Case Insensitive, should be easy but is not!

[doubleclick]

Hello everyone! 

I want to replace any instance of Ltd, whether it's ltd or LTD or Ltd that will appear as part of a string with a nothing. Plus, i have a few other words i want to do this with 

I did a simple right click, replace text and have been experimenting with where to put the comparer.orignalignorecase function but unable to determine!

Here's my formula:

= Table.ReplaceValue(#"Renamed Columns","LTD","",Replacer.ReplaceText,{"Supplier (cleansed)"})

Os anyone smart enough to help?

[Rickmaurinus]

Hi there,

If you purely want to remove a certain string, the marked solution works fine. If you want to replace the string with something else you can use the method in chapter 6.4 of my article: Replace Values in Power Query M (Ultimate Guide) - Gorilla BI

= Text.Combine(
     List.ReplaceMatchingItems (
          Text.Split( [Text], " " ) ,
          { { "ValueToReplace", "ValueToReplaceWith" } },
          Comparer.OrdinalIgnoreCase ),
     " ")

If you only want to remove List.RemoveMatchingItems works perfectly fine 🙂

Regards,

Rick de Groot

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

@ 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.

[Vijay_A_Verma · Accepted solution]

You can use below formula in a custom column

= Text.Combine(List.RemoveMatchingItems(Text.Split([#"Supplier (cleansed)"]," "),{"ltd"},Comparer.OrdinalIgnoreCase)," ")

[doubleclick]

YOU SIR, ARE A GENIUS!

[mahoneypat]

Here's one way to do it.

In Transform/Format - make the column lower case

With Replace Values - replace " ltd" with ""

In Transform/Format - choose Capitalize Each Word

Pat

[doubleclick]

No i cannot, it ruins the whole column...

Not sure why this one problem is so difficult for you powerbi geniuses!

[Greg_Deckler]

@doubleclick Try something like:

= Table.ReplaceValue(
     #"Changed Type",
     each [Consumer_ID], 
     each if Text.Contains([Consumer_ID] ), "rick", 
           Comparer.OrdinalIgnoreCase ) then "Rick" else 
           [Consumer_ID],
     Replacer.ReplaceValue,
     {"Consumer_ID"}
 )

Replace Values in Power Query M (Ultimate Guide) - Gorilla BI

[doubleclick]

Not sure this works. 

Say there's 3 entries:

1) Microsoft LTD

2) Google Ltd

3) Amazon ltd

I want the text replace to replace any instance of ltd with nothing, to delete it so i'm only left with the company name