cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Noob92
Regular Visitor

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.

Noob92_0-1661807565869.png

 

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.

Noob92_1-1661808294548.png

 

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.

Noob92_2-1661808576889.png


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.

 

2 REPLIES 2
Adel
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! 

mahoneypat
Microsoft
Microsoft

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

 

Pat

 





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

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.