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