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- Column...
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 events
left join UNNEST(event_params) as new_event_params
This 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.
SELECT
distinct(user_pseudo_id),
event_date,
geo.continent,
geo.country,
geo.region,
geo.city,
geo.sub_continent,
geo.metro
FROM "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.
Hope that gives you some light on the situtation. Really not a ideal method and does take a lot of time to work it.