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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Ckg_PBI
New Member

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.

 

 unnest_params_ok.png

 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

unnest_geo_ko.png

Schema in BigQuery

geo_schema.png

Table Preview in BigQuery - geo values are split in columns

geo_table.png

 

Table loaded in PowerQuery

PwbI_geo.png

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

 

 

 

3 REPLIES 3
QueryWhiz
Helper IV
Helper IV

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:

 

GA4-1.png

 

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:

 

GA4-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

Kieran_Ultra_23
Frequent Visitor

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-a7793117... 

Hope that gives you some light on the situtation. Really not a ideal method and does take a lot of time to work it. 
Ckg_PBI
New Member

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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors