Hi,
Background to my issue: I have monthly data for two years for around 10 regions.
Each month for each region is a separate table. So what i have done is appended 12 months tables into 1 new table for each year by region; and then subsequently appended this yearly tables by region. This gives me 10 master tables [i disabled load on all old tables to save time and memory]. Its based on these 10 tables that i have written all my measures.
Now the problem is none of these 10 tables aren’t loading in the power query. It was working fine until a few days back. Now it just keeps loading forever; and system heats up running this. Pls help me solve this. I have been working on this model for weeks now.
@Jackson
I’m obviously not Sam, but is there a particular reason you are using 10 master tables? This seems perfect to set up power query to import all the files from a folder and just go from there? But maybe I’m missing something.
I thought i will take this route initially where files are directly extracted from a folder using power query. But then realised it may not work as the monthly tables aren’t standard every month. There could be new columns or rows being added in some months.
And even if i query them in this manner suggested, i would still need to create 10 master tables because each table is in a different currency and the columns in each of these master tables are unique.
To address your primary issue regarding the data load, I recommend one of two options:
Creating a new blank test report and then add each current data source into it fresh to see if the load starts working. This will let you see if some kind of corruption in the PBIX due to numerous changes over time is causing an issue.
Create a complete copy of the original report, then remove or change one thing at a time to see if it affects the load. I would start by deleting all old tables that are no longer needed (so only keep the current 2 years of data). This will let you potentially isolate the task(s) or data component(s) that are causing the issue.
On a related note, if you only need the same final columns from all tables for your measures, then it is possible to create a separate staging table for each unique monthly table and then use the extremely powerful transformation tools in Power BI to merge them into one “long and thin” table. This would be a separate forum discussion though
If you determine what is blocking your load process, please post the results back for all of us to know about.
To add on these comments @Jackson I sometimes also find what helps speed up the Power Query for data loads etc are:
-Check to see if your cache has been freed up?
-Check your settings and privacy settings.
or
-It sounds likes what your doing is just uploading all files from a folders and then doing the ETL. I am assuming you probably have a lot of ETL steps on the right side corner tab to show steps. This really slows down PQuery. What I tend to do is create a parameter within the M-Code this way it significantly reduces your ETL steps and the M-code does the heavy ETL for u.
