Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Not applicable

Google Analytics to Big Query to Power Bi Data Transformation

Dear Experts, 

I am relatively new to power bi please guide me if I am using the correct method to load and transform my data, I connected Google Analytics to Big Query and then Big Query to Power Bi (Did this because the GA connecter was not connecting).  The screenshot below shows how the data tables show up.



Every day it gets updated with a new event table, each event table is the data of a new date.

I manually import the new event table to power bi desktop, the next thing I do in Power Query Editor is that I have this one fact table event_20220811 on which I have done all the transformations.



I applied Appended Query step at the start, every time I add a new data table I manually change the formula and enter the name of the new data table, it simply adds the new table and applies the same transformation.  Screenshot Below.


The problem is that every time a new data table is added by this method, when I hit close and apply power bi takes more time to load. I have approx. half a million rows and data is expected to grow up to a billion in the future. Since I am only a beginner I don't know if the approach I am applying to load and transform this data is the right choice. 

So far the data is small and it works out for me but I am worried that as the data will grow this method will fail as load time will be too much. Can someone please guide me on a better approach or solution to this please? Thanks.


Helper III
Helper III

hey, i know of a video that can guide through google analytics to bigquery and then power bi.
Hope it helps! 


It is likely possible to automate and speed up what you are doing, and do it all with a single query (not separate queries/tables for each date). It would be a more advanced query, but you can autogenerate a table of dates in the correct text format, prefix them with "events_", and then add a custom column where it queries the events table for that date, so you can then append them all together (with Table.Combine). If you also create a Datetime column along with your original date as text column, you can also set up incremental refresh so you don't have to reload data you've loaded previously with each refresh.


If you get stuck trying that, @ mention me in a reply with the M code from the Advanced editor for one of your existing queries (removing any sensitive info like keys, etc.).




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors