Google BigQuery Pull data from tables where each table is a separate date

[ScrubberKing]

Hello,

I am trying to pull GA4 events data from Big Query but events are each stored in their own table partitioned by date. Each data source looks like the following:

I want to combine all of these tables into one table with the ability to refresh each day to grab the previous days data once it gets loaded. 

I tried to use a function to do this with the following code:

(date as number) as table=>

let
    Source = GoogleBigQuery.Database(),
    #"nilfisk-ga4-export" = Source{[Name="nilfisk-ga4-export"]}[Data],
    analytics_311943674_Schema = #"nilfisk-ga4-export"{[Name="analytics_311943674",Kind="Schema"]}[Data],
    events_20220623_Table = analytics_311943674_Schema{[Name="events_""&Number.ToText(date)&",Kind="Table"]}[Data]
in
    events_20220623_Table

 and then created a date table to invoke the function for each date but every time I was getting an error that the given date did not contain any data when I went to expand the fields:

Any tips on how to do this? I tried following along with the following tutorials but no luck:

https://www.youtube.com/watch?v=6PZSZ53iSos&t=395s 

https://community.fabric.microsoft.com/t5/Power-Query/Importing-and-merging-multiple-Google-BigQuery...

[MongooseGeneral · Accepted solution]

I had the same issue and found a tutorial suggesting using the following line in the query that you enter in Advanced Options under the BigQuery conector in Power BI. I think I got it from here: Page dimensions & metrics (GA4) (ga4bigquery.com)

from

    `projectid.analytics_311943674.events_*`

That should bring them all together.

E.g. this is what I use to get pageviews:

select

    (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') as page,

    countif(event_name = 'page_view') as page_views,

    event_date

from

    -- change this to your google analytics 4 export location in bigquery

       `projectid.analytics_311943674.events_*`

where

    -- define static and/or dynamic start and end date

    _table_suffix > '20230301'

group by

    page,

    event_date

order by

    page_views desc

[ScrubberKing]

Hey! This worked! Thank you so much for sharing. Saved me a lot of headaches. 

[v-shex-msft]

HI @ScrubberKing,

Perhaps you can consider switching to ODBC connector to getting data from bigquery data source, it allow you to use advanced query to query and customize the result data.

How to connect BigQuery to Microsoft Excel and other apps with our new ODBC driver | Google Cloud Bl...

Regards,

Xiaoxin Sheng