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
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?
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
Sorry - forgot to add - no sliders, just the six pivot tables
OK, so you have one query that combines all files from the Data folder. What are the other 79 queries doing?
I don't know!!!
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
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
@prof_fr Good luck! If you get stuck, come back here.
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
Maybe this is what you are looking to do
