Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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.
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. 🙂
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.
@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])
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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |