Google Analytics to Big Query to Power Bi Data Transformation

[Anonymous]

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.

[Adel]

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

Tutorial Article: https://windsor.ai/how-to-get-your-analytics-crm-and.../

Tutorial Video:https://www.youtube.com/watch?v=2dsTEKTvfMc&t=8s&ab_channel=Windsor.aiPerformanceMarketing

[mahoneypat]

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