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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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! 

Microsoft Employee
Microsoft Employee

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

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.