Obsolete Named Ranges causing slow Excel Table query

[GooberTron]

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?

 

[That Harry There]

Thank you for this - I've been trying to debug a query that was pulling in data from a URL that I pick up from a cell in the spreadsheet. It was running ridiculously slow and I kept trying to simplify to troubleshoot up to the point I had a query that was just selecting the text from one cell in an otherwise empty spreadsheet and this was still slow as hell.

The Name Manager revealed that there were a couple of invalid name references and one referencing a range in another excel file on another location which I couldn't access. Deleting these obsolete names seems to have fixed the problem