The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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])
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
36 | |
23 | |
23 | |
18 | |
16 |