Remove text between delimiters

[Anonymous]

Hi,

I’m looking for a way to remove the text between two delimiters. I’ve tried these two solutions but unfortunately they do not work for me.

https://community.powerbi.com/t5/Desktop/Remove-text-between-delimiters/m-p/303937

https://community.powerbi.com/t5/Desktop/Power-Query-remove-all-text-between-delimiters/m-p/460854

The problem is as follows.

I have a table with 3 million rows of product information:

Product input

Bla Bla Bla 7LITER

bla 5LITER text text

Blab la text

4LITER text bla

Bla 4.0LITER text

Text bla LITER50 bla text

Bla bla 233.3LITER text bla 10LITER text text

As you can see the product information can contain the volume of the product, but sometimes it will not contain that info. The position of the volume information (LITER) is different for each row, this is only a small example of some of the types of ways the information is being displayed. Next to that sometimes the LITER precedes the amount, and sometimes the amount will be shown before the LITER, and sometimes the amount of Liters will have a decimal in the number.

I want to remove the volume information from all rows so that the output looks like this:

Product input	Product output
Bla Bla Bla 7LITER	Bla Bla Bla
bla 5LITER text text	Bla text text
Blab la text	Blab la text
4LITER text bla	text bla
Bla 4.88LITER text	Bla text
Text bla LITER50 bla text	Text bla bla text
Bla bla 233.3LITER text bla 10LITER text text	Bla bla text bla text text

A solution would be to simple do a search and replace for all possible LITER combinations, but that will result in hundreds of search and replace queries, which has a big impact on refresh time. How can I get to the output with the least amount of processing power?

[Nathaniel_C]

@Anonymous ,

Perhaps it will be either a function that removes any word that "contains" Liter, or inserting a delimiter around any word that contains the same.  Calling on the M language superheroes.  @ImkeF @KenPuls .

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Yes such a solution would indeed do the trick!

[ImkeF · Accepted solution]

Hi @Anonymous ,

You can create a column with the bolded code - if you paste the full code into the advanced editor you'll have a complete solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspJVIBhcx/PENcgpVidaKUkINcUzFUoSa0oARNgCaC6JAWgJFzABEkVUBdMkYKJngFCBiwaAlWiABY3NQCzkQ0GCxgZG+sZo5qpYGiA4ZRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Product input" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product input", type text}}),
    #"Added Custom" = 
        Table.AddColumn(
            #"Changed Type", 
            "Custom", 
            each Text.Combine(
                    List.Select(
                        Text.Split([Product input], " "), 
                        (x) => not Text.Contains(x, "liter", Comparer.OrdinalIgnoreCase)), 
                    " "))
in
    #"Added Custom"

.. unfortunately the new editor doesn't let me highlight parts of the code anymore - this is the code that goes into the "Table-Add-column-Window":

Text.Combine(
                    List.Select(
                        Text.Split([Product input], " "),
                        (x) => not Text.Contains(x, "liter", Comparer.OrdinalIgnoreCase)),
                    " ")

[Anonymous]

@ImkeF  that works perfectly, thanks so much for your help!

BTW, are there any books or courses you'd recommend to become better at writing M?

Regards

Bas