Forum

Power pivot/Power Q...
 
Notifications
Clear all

[Solved] Power pivot/Power Query problem

11 Posts
3 Users
1 Reactions
154 Views
(@prof_fr)
Posts: 26
Eminent Member
Topic starter
 

My refresh all has decided it want to take a l o n g time to complete its task.

I have eight pivot tables taking data from one data file which contains 156 Excel files (each contains data from one of our businesses over the past 15 months).

Of late it seems to be working very slowly - spending a long time "contacting the datasource" before whizzing through the actual update.

Any ideas how to speed the process?

There are 80 queries and 0 connections in the "queries and connections" area which appears on the right of the screen.

Thanks  in advance

Regards

Chris

 
Posted : 07/07/2025 3:26 am
Riny van Eekelen
(@riny)
Posts: 1263
Member Moderator
 

@prof_fr

You write "from one data file which contains 156 Excel files". Do you mean one file with data that originates from 156 other files? If so, did you combine all the files with PowerQuery with File, From Folder? Please explain a bit more what the 80 queries are doing.

Recently, I worked on a fairly simple model myself with data from 4 small tables. All updated smoothly until I decided to add a few pivot tables, charts and 6 or 7 slicers. A Refresh became noticeably slower. Did you perhaps add a lot of Slicers to the pivot tables recently?

 

 

 
Posted : 07/07/2025 2:54 pm
(@prof_fr)
Posts: 26
Eminent Member
Topic starter
 

Good morning Riny

Thankyou for your reply.

Apologies for using the wrong terms. Each file contains the data for one month "...2025_06" for example. They are one folder "...data" and the pivot tables were made using the "data-from folder" etc query.

I used to have many more pivot tables but have pared this down tojust six.

 

Hope that helps

Thanks

Chris

 

 
Posted : 07/07/2025 4:56 pm
(@prof_fr)
Posts: 26
Eminent Member
Topic starter
 

Sorry - forgot to add - no sliders, just the six pivot tables

 
Posted : 07/07/2025 5:59 pm
Riny van Eekelen
(@riny)
Posts: 1263
Member Moderator
 

OK, so you have one query that combines all files from the Data folder. What are the other 79 queries doing?

 
Posted : 07/07/2025 8:13 pm
(@prof_fr)
Posts: 26
Eminent Member
Topic starter
 

I don't know!!!

 
Posted : 07/07/2025 8:15 pm
(@prof_fr)
Posts: 26
Eminent Member
Topic starter
 

What I did do was take a blank workbook, data, from file, from folder etc

I then loaded 4million plus rows !

clearly something is amiss.

Yesterday I went through all 160 worksheets and made sure the tables ended where the data ended. So I am now at a loss to see where these extra rows and three errors have come from.

Perhaps I ought to redo the check for the parameters of the tables

 
Posted : 07/07/2025 8:20 pm
(@prof_fr)
Posts: 26
Eminent Member
Topic starter
 

Sorted!!!

I looked in the file explorer at the files in data folder - most were 30kb or so...but one was 90k and two were 45k.

So I opened those files, and while the table was showing the correct a1 to z60 parameters there were lot of blue gridlines below that.

I copied and pasted just the table into column aa then delected the "blank" columns a to z

That cured it.

 

Thank you for your help - your questions made me look closely at the tables.

My "new" pivot has found £511.50 in a date before the table were meant to start... off we go again 🙄 

Kindest regards

Have a good day

Chris

 
Posted : 07/07/2025 9:17 pm
Riny van Eekelen
(@riny)
Posts: 1263
Member Moderator
 

@prof_fr Good luck! If you get stuck, come back here.

 
Posted : 07/07/2025 10:58 pm
(@prof_fr)
Posts: 26
Eminent Member
Topic starter
 

Hi 

I wondered if you could  help. Is there a way of cobining all the worsheets in my data folder into one worksheet - solely to make it easier to hunt down the pre-pivot date entry.

Thanks

Chris

 
Posted : 08/07/2025 12:27 am
Alan Sidman
(@alansidman)
Posts: 235
Member Moderator
 

Maybe this is what you are looking to do

https://www.youtube.com/watch?v=vfskquGoeG4

 
Posted : 08/07/2025 7:01 am
Share: