Obsolete Named Ranges causing slow Excel Table query

GooberTron

Board Regular
Joined
Oct 13, 2012
Messages
205
I was gifted a Workbook from a colleague and asked to help set-up a simple Pivot Table.
Their existing table needed a bit of re-arrangement, so I loaded it as a query and unpivoted some things, added to data model and added a calculated column. Source Excel table was perhaps 70 rows and 20 columns, so really tiny.
However, refreshing the Power Pivot Tables I created from the Data Model was taking nearly a minute.
I stripped all the steps back, tried every weird tweak from Google on speeding up data loading. Nothing was working.
The answer was when I checked the Name Manager. Hundreds of obsolete Workbook scope named ranges, referring to old sheets or references, some referring to network shares etc. Once I deleted all of these, refresh time was almost instant - a couple of seconds max.
What's the logic of Power Query getting stuck on all these named ranges, given that they seemed to not really relate to the Table / query in question? I'm guessing the network share type references were the ones killing it, but this means Power Query does something to check these on every refresh?
 

Forum statistics

Threads
1,078,373
Messages
5,339,814
Members
399,330
Latest member
mallikharjun

Some videos you may like

This Week's Hot Topics

Top