Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fmouhcine
Frequent Visitor

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'

 

URLTTL_viewsDate
URL_11871/1/2024
URL_11611/2/2024
URL_1431/3/2024
URL_1921/4/2024
URL_1481/5/2024
URL_11731/6/2024
URL_2651/1/2024
URL_2491/2/2024
URL_21841/3/2024
URL_2861/4/2024
URL_2921/5/2024
URL_21531/6/2024
URL_3351/1/2024
URL_31721/2/2024
URL_31811/3/2024
URL_3711/4/2024
URL_31131/5/2024
URL_3501/6/2024
URL_4481/1/2024
URL_41441/2/2024
URL_41261/3/2024
URL_41671/4/2024
URL_4571/5/2024
URL_41681/6/2024
URL_5821/1/2024
URL_51531/2/2024
URL_5371/3/2024
URL_51551/4/2024
URL_51041/5/2024
URL_51611/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 🙂 )

 

 

 

2 ACCEPTED SOLUTIONS
dufoq3
Super User
Super User

Hi @fmouhcine, you can us such filter expression (add this as new step, not as a new custom column)

dufoq3_0-1713956741235.png

 

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

8 REPLIES 8
ReportGuru
Helper V
Helper V

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:

 

GA-1.png

 

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:

 

GA-2.png

 

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.

 

SELECT_DESTINATION_NEW.png

dufoq3
Super User
Super User

Hi @fmouhcine, you can us such filter expression (add this as new step, not as a new custom column)

dufoq3_0-1713956741235.png

 

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

Could you provide screenshots of both tables please?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi @dufoq3 

 

Please see below the tables' screenshots as requested:

 

"Data" source table :

 

fmouhcine_0-1713964875793.png

 

"Filter" source table:

fmouhcine_1-1713965110035.png

 

"Data" source table after applying the filter:

fmouhcine_2-1713965189803.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors