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

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.

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