Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove text between delimiters

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-b...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

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

     

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

Yes such a solution would indeed do the trick!

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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