Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter out unique rows which follow the same text pattern

I have Google Analytics data and I have 102 rows (and counting) that hold data I need to filter out in Query Editor. 

 

The column is called [Page] and below are examples of the data.

 

Is there a way to filter these out based on their pattern? 

Bear in mind I also have relevant rows which start with /appointment/

 

/appointment/e00ef7c5-22b8-e911-a82d-002248008902remove
/appointment/e04a7bb3-a0a3-e911-a827-002248008a67remove
/appointment/e098c58i-4db8-e911-a82d-002248008902remove
/appointment/e0fbe914-6daf-e911-a82a-0022480084d9remove
/appointment/ee1a325d-feca-e911-a812-000d3a86d756remove
/appointment/bookkeep
/appointment/contactkeep
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

    Sorry for misunderstanding. For the sample data, the rows that you want to remove contain "-". If all the actual data is like this, you could try:

    = if Text.Contains([Column1],"-") then null  else [Column1])

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc09CoQwEEDhu6Q2OJn8n2WxmCQTENHIEvb8a7NaCBZbPz7e6yVG2vc2b33lrY8MwNVnKxFTkByVkhSwSABEEwBCBBSDePPaPiym4cYN+ZS0JCB9cn9xcv6Rx5BtmKUp/91rOpCRrlA9OV3clPjEWZFGW2TlTD+u8OBQNAVXvHUPPLW2HHlh3u8xt61T7mefvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"-") then null  else [Column1])
in
    #"Added Custom"

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Thim's avatar
    Thim
    Resolver V
    6 years ago

    Could you provide a larger data sample, that also shows some of the values you want to keep?

     

    This will help to make sure our advice concider not removing those values you need.

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi Anonymous ,

    You could try to use the function of Text.Contains. Please see the details below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndDPDoIwDAbwd+HMQhmDjWcxHLq1RCQwIkTUp5f4BwJGDl566Jff16SHQxChdfQcfRAGZ278hYMi3AY1c/ded52v2qHhdogYgEvtUiGlNYLzOBZoJAkAKZUBMDnIbeuKK9TWJgIBk5nrhWOmd3luXGoqoei/66WdkBIZYTlzXLiifI9zjIlMSZTs8MNjOXGgBE1GOs12uPW+/vlX59sB3bDKr7d707dRf6TTONKm+St8ueIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"/appointment/") then [Column1] else null)
in
    #"Added Custom"

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-xuding-msft  I actually ended up doing something similar - I used extract after delimiter but that meant also removing those that I wanted to keep e.g. /appointment/book and /appointment/contact but it seemed there was no solution which involved using wildcards to depict the specific pattern I wanted to filter out. 

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        Hi Anonymous ,

        Sorry for misunderstanding. For the sample data, the rows that you want to remove contain "-". If all the actual data is like this, you could try:

        = if Text.Contains([Column1],"-") then null  else [Column1])

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc09CoQwEEDhu6Q2OJn8n2WxmCQTENHIEvb8a7NaCBZbPz7e6yVG2vc2b33lrY8MwNVnKxFTkByVkhSwSABEEwBCBBSDePPaPiym4cYN+ZS0JCB9cn9xcv6Rx5BtmKUp/91rOpCRrlA9OV3clPjEWZFGW2TlTD+u8OBQNAVXvHUPPLW2HHlh3u8xt61T7mefvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"-") then null  else [Column1])
in
    #"Added Custom"

        Best Regards,

        Xue Ding

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Thim's avatar
    Thim
    Resolver V
    6 years ago

    You add an additional column that looks after the value -

    If you set it up like this, then you can easily remove those who spits out the value "Remove"

     

    Hope this will help. 🙂