How to query Google Analytics 4 data via BigQuery into Power BI? Dynamic Table name?? Help!

In case anyone else stumbles across this question, I had the same problem and MongooseGeneral provided a perfect solution which does not necessitate appending data. See here: https://community.fabric.microsoft.com/t5/Desktop/Google-BigQuery-Pull-data-from-tables-where-each-table-is-a/m-p/3290482#M1101267 

MongooseGeneral  wrote:

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

---