Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 🙂 )
Solved! Go to Solution.
Hi @fmouhcine, you can us such filter expression (add this as new step, not as a new custom column)
Replace:
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_FilteredByTable2URLs
If 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])))
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.
Hi @fmouhcine, you can us such filter expression (add this as new step, not as a new custom column)
Replace:
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_FilteredByTable2URLs
Hi @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
Hi @dufoq3
Please see below the tables' screenshots as requested:
"Data" source table :
"Filter" source table:
"Data" source table after applying the filter:
If 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])))
Many Thanks @dufoq3 ! it works perfectly !!
You were right; I realized that missed the character "/" at the end of each URL in my filters' list, this is why it was not working - my bad ^^!!!
Last but not least : will that be possible to have in my filter the condition : Data[pagePath] contain the string Filter[URL to consider] - instead of Data[pagePath] matches exaclty Filter[URL to consider]
OK, so we probably do not need any Text.Trim:
Table.SelectRows(#"Added Items", each
[ a = List.Buffer(URLs[Url]),
b = List.AnyTrue(List.Transform(a, (x)=> Text.Contains([pagePath], x)))
][b])
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
16 | |
10 | |
9 | |
9 | |
8 |
User | Count |
---|---|
23 | |
16 | |
14 | |
12 | |
12 |