Filter Imported Google Analytics data from pre-defined list in Excel

[fmouhcine]

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

[dufoq3 · Accepted solution]

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_FilteredByTable2URLs

[ReportGuru]

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.

[dufoq3 · Accepted solution]

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_FilteredByTable2URLs

[fmouhcine]

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

[dufoq3]

Could you provide screenshots of both tables please?

[fmouhcine]

Hi @dufoq3 

Please see below the tables' screenshots as requested:

"Data" source table :

"Filter" source table:

"Data" source table after applying the filter:

[dufoq3 · Accepted solution]

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

[fmouhcine]

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]

[dufoq3]

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