Forum Discussion
Error GA4 import from BigQuery to PowerBI tables columns unnested values record types
Hello I am loading data of GA4 in PowerBI , no problem on connection and loading the data , but when accessing at columns tables there are some important problems regarding nested alues
1- Columns as event_params or user_properties needs to be unnested previously in Bigquery to create new columns with this data , it can be done with a simple sentence of SQL and once done does not represent any problem to load in PwBI.
2- Other columns that as device or geo ( Record Types) that are not nested in BigQuery ( it´s not possible to unnest there) , in the table of Bigquery are in different columns but PowerQuery read as if were nested values , probably loading the schema instead of the table.
Not possible to unnest
Schema in BigQuery
Table Preview in BigQuery - geo values are split in columns
Table loaded in PowerQuery
Power Query does not load the individual columns of Geo data, instead loads only the Geo column of the schema.
I couldn´t get those values with Json column split or other methods within PowerQuery.
Te question is HOW can we make that PowerQuery loads the splitted columns in BigQuery table instead the schema of the table?
I´d appreciate any help !
Best regards
4 Replies
Hello all @experts, really there is nobody who has faced and/or resolved this issue , nobody is importing GA4 data from BigQuery?.. I think is a major issue of PowerQuery
Thank you!
Hi Ckg_PBI I am currently working on GA4 data and importing the data into Power BI. You can't just connect the data into Power BI due to the nested colunms. I have needed to transform and model the data in Big query before importing it into Power BI.
The first piece of code i used was this :
SELECT new_event_params.*, events.* except (event_params)from "name_of_ data_table" as eventsleft join UNNEST(event_params) as new_event_paramsThis unnests the event params column. However this is the first step i did and i have had to do alot of tranforming and modelling outside of Power BI to use the data. Also had to use the schedule queries function in BigQuery to make sure the saved queries actually refresh on a daily basis before pulling into Power BI.For the geo location i used this code to create a Dim table with the user_pseudo_id as a primary key to link to the other tables.SELECTdistinct(user_pseudo_id),event_date,geo.continent,geo.country,geo.region,geo.city,geo.sub_continent,geo.metroFROM "Table"Some other considereations and useful pages i used when working with GA4 data here-https://towardsdatascience.com/how-to-query-and-calculate-ga-app-web-event-data-in-bigquery-a77931176d3
Hope that gives you some light on the situtation. Really not a ideal method and does take a lot of time to work it.Hi Ckg_PBI In this case as a workaround I think you can go directly from GA4 to PBI without using a DW. You can do that using for example an external connector. In our agency we use windsor.ai to pull GA4 data directly and once data comes to PBI we don't an issue like the one you have with BQ, because fields get pulled without those issues. In case you wonder, to make the connection first search for the GA4 connector in the data sources list:
After that, just grant access to your GA4 account using your credentials, then on preview and destination page you will see a preview of your G4 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.
Set up a daily load from big query to a warehouse. Unnest the data in your pipeline. I used Data Factory (retrieve your data from bigquery using query as your source. Write bigquery SQL to unnest within ADF) that worked for me.
