Filter out unique rows which follow the same text pattern

[Anonymous]

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-002248008902	remove
/appointment/e04a7bb3-a0a3-e911-a827-002248008a67	remove
/appointment/e098c58i-4db8-e911-a82d-002248008902	remove
/appointment/e0fbe914-6daf-e911-a82a-0022480084d9	remove
/appointment/ee1a325d-feca-e911-a812-000d3a86d756	remove
/appointment/book	keep
/appointment/contact	keep

[Thim]

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

[v-xuding-msft]

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]

@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 · Accepted solution]

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]

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.