Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
1 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])

1.PNG 

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.

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.

View solution in original post

5 REPLIES 5
Thim
Resolver V
Resolver V

You add an additional column that looks after the value -

image.png

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
Community Support
Community Support

Hi @Anonymous ,

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

1.PNG2.PNG

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.

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
Not applicable

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

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])

1.PNG 

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.

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
Resolver V
Resolver V

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.