Excel Power query performance / memory errors

[piannetta]

Hi,

I have a spreadsheet, file size just over 2MB, file contains about a dozen queries. Not a huge amount of data, some table joins but not overly complex.

What I've started to find is that when refreshing the queries, the memory usage (looking at Windows Resource Monitor) jumps rapidly from about 190mb up to 2.5GB, even if I just refresh one of the queries.

It was popping up file corruption errors at one point, so I created a new spreadsheet and I'm still having the same problems.

It was working fine and I'm frustrated that I can't seem to identify an offender for these recent issues.

Would appreciate any tips on how I can diagnose these issues.

I'm also looking to upgrade my Excel to 64-bit to see if that helps, but ideally, I'd like to find the root cause of the problem. I'm currently using Excel 2016 (16.0.9330.2124) 32-bit

Cheers,
Pete

 

[Ron Coderre]

I was running into this problem at the worst possible times. Fortunately, Ken Puls explained what was happening to me.
I needed to select Power_Query.Options.Data_Load and UN-check: Allow data preview to download in the background.
Evidently, that option is inaccurately described. It implies that only the current query's preview will be downloaded, when actually the previews for all queries are downloaded whenever any query is edited.

Does that help in your situation?

 

[piannetta]

Hi Ron, thanks for your quick reply. I've changed that setting, and so far so good. Thanks!

 

[andydtaylor]

Most useful post ever, thanks!