Forum Discussion
Filter Imported Google Analytics data from pre-defined list in Excel
Dear Community,
I do have to import data from Google analytics to check the total views per URL.
Below is an example of the GA data stracture I'm importing:
Table name = 'Data'
| URL | TTL_views | Date |
| URL_1 | 187 | 1/1/2024 |
| URL_1 | 161 | 1/2/2024 |
| URL_1 | 43 | 1/3/2024 |
| URL_1 | 92 | 1/4/2024 |
| URL_1 | 48 | 1/5/2024 |
| URL_1 | 173 | 1/6/2024 |
| URL_2 | 65 | 1/1/2024 |
| URL_2 | 49 | 1/2/2024 |
| URL_2 | 184 | 1/3/2024 |
| URL_2 | 86 | 1/4/2024 |
| URL_2 | 92 | 1/5/2024 |
| URL_2 | 153 | 1/6/2024 |
| URL_3 | 35 | 1/1/2024 |
| URL_3 | 172 | 1/2/2024 |
| URL_3 | 181 | 1/3/2024 |
| URL_3 | 71 | 1/4/2024 |
| URL_3 | 113 | 1/5/2024 |
| URL_3 | 50 | 1/6/2024 |
| URL_4 | 48 | 1/1/2024 |
| URL_4 | 144 | 1/2/2024 |
| URL_4 | 126 | 1/3/2024 |
| URL_4 | 167 | 1/4/2024 |
| URL_4 | 57 | 1/5/2024 |
| URL_4 | 168 | 1/6/2024 |
| URL_5 | 82 | 1/1/2024 |
| URL_5 | 153 | 1/2/2024 |
| URL_5 | 37 | 1/3/2024 |
| URL_5 | 155 | 1/4/2024 |
| URL_5 | 104 | 1/5/2024 |
| URL_5 | 161 | 1/6/2024 |
However I will need to check the total views for specifics URLs only (Want to import data only for these URLs) - to do so, I will have to define each time a list of URLs in an excel file, and I want to filter my GA imported data based on the URLs that will be listed into this excel file. Example below:
Table name = 'Filter'
| URL to consider |
| URL_1 |
| URL_2 |
| URL_5 |
Is there any way to filter data from the source using a fixed/pre-defined Excel list?
Thanks in advance for your support (as usual 🙂 )
Hi fmouhcine, you can us such filter expression (add this as new step, not as a new custom column)
Replace:
- Table1_Data with your previous step reference
- Table2_Filter[URL to consider] where Table2_Filter is a table and [URL to consider] is column
Table.SelectRows(Table1_Data, each List.Contains(List.Buffer(Table2_Filter[URL to consider]), [URL]))Whole sample query:
let Table1_Data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdJBCsQgDIXhu7gu1MTEpHeY1UBXpcz9bzEkMtTBt3Hh3+iH9LrK+X59qGyF3GLdaefKUu5tSj3XndckLUtby8FZBMx4FgUX2Tiu/6c4qSvSRZED4fJ2F6SL5B3p+HErOE6hLjYb1OXnxoiXyQnxIhkhXg5RQ77Y1Ip48rw4rYVEEC83uSNepm7IF0kN8caQI1+8nDPy6fTmvKZmiDeGFPFyswry6fSX/3z3Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [URL = _t, TTL_views = _t, Date = _t]), Table2_Filter = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg3yiTdUitWBsIzgLFOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"URL to consider" = _t]), Table1_FilteredByTable2URLs = Table.SelectRows(Table1_Data, each List.Contains(List.Buffer(Table2_Filter[URL to consider]), [URL])) in Table1_FilteredByTable2URLsIf you are 100% sure, that you have /ar/national-auto/get-a-quote and ar/national-auto/find-us in GA-Data table try to use this:
Table.SelectRows(#"Added Items", each List.Contains(List.Buffer(List.Transform(URLs[Url], Text.Trim)), Text.Trim([pagePath])))
8 Replies
Hi fmouhcine, you can us such filter expression (add this as new step, not as a new custom column)
Replace:
- Table1_Data with your previous step reference
- Table2_Filter[URL to consider] where Table2_Filter is a table and [URL to consider] is column
Table.SelectRows(Table1_Data, each List.Contains(List.Buffer(Table2_Filter[URL to consider]), [URL]))Whole sample query:
let Table1_Data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdJBCsQgDIXhu7gu1MTEpHeY1UBXpcz9bzEkMtTBt3Hh3+iH9LrK+X59qGyF3GLdaefKUu5tSj3XndckLUtby8FZBMx4FgUX2Tiu/6c4qSvSRZED4fJ2F6SL5B3p+HErOE6hLjYb1OXnxoiXyQnxIhkhXg5RQ77Y1Ip48rw4rYVEEC83uSNepm7IF0kN8caQI1+8nDPy6fTmvKZmiDeGFPFyswry6fSX/3z3Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [URL = _t, TTL_views = _t, Date = _t]), Table2_Filter = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg3yiTdUitWBsIzgLFOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"URL to consider" = _t]), Table1_FilteredByTable2URLs = Table.SelectRows(Table1_Data, each List.Contains(List.Buffer(Table2_Filter[URL to consider]), [URL])) in Table1_FilteredByTable2URLsHi dufoq3
Many thanks for your reply !
When I tried the formula
Table.SelectRows(Table1_Data, each List.Contains(List.Buffer(Table2_Filter[URL to consider]), [URL]))it applies the filter using only the values of the 1st record in my table "Table2_Filter", however I will need to filter by all URLs listed into the "Table2_Filter"
Many thanks
Could you provide screenshots of both tables please?
Hi fmouhcine Is this UA or GA4 data that you are talking about? As a workaround, maybe you can try to test your connection with a 3rd party connector that pulls data directly from GA, so you won't have to export the data to an excel file and you'll be able to pre-filter the data on the tool UI, so once you export it to PBI your data will be ready. I currently use windsor.ai to connect all my UA data. In case you wonder, to make the connection first search for the GA connector in the data sources list:
After that, just grant access to your GA account using your credentials, then on preview and destination page you will see a preview of your GA fields:
There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.
