Power Query memory increasing when data connections refreshed simultaneously (manually or via VBA)

siers 16

Hello all,

I've been coming across an issue using Power Query in Excel 365 (Enterprise E3, both 32 and 64 bit version).

I have 4 Power Query data connections using an API query to retrieve JSON data automatically every 10 seconds, transform into a table.

I'm finding if I use VBA to initiate a Refresh All every 10 seconds, the Excel application memory usage just keeps growing until the RAM fills up.

Initially I was running 32-bit Excel and it would throw .NET out of memory errors at around 1.2GB, so I upgraded to 64-bit to see if it would stabilise at all.

However after running for a few hours, it was up to 4.5GB of RAM utilisation so it seems like there is some form of memory leak.

I've been through many forum entries looking at other suggestions to no avail:

upgrading to 64-bit Excel
turning off Data preview,
turn off Background refresh,
disable Auto-recover
turn off telemetry
load to data table
turning on (and off) Fast Data load

I've also gone to the effort of creating a brand new worksheet using some dummy data (from https://www.oauth.com/playground API's) , with 4 brand new queries with only basic transformation (expand to table), and no other formulas nor any conditional formatting.

Plotting the memory working set over 30 mins, after opening (142mb) and before first refresh (215mb), after the first refresh it starts at 480mb and keeps just increasing (adding nearly 500mb in 30 mins).

The issue only really seems to happen if refreshing all queries simultaneously - including if manually clicking 'Refresh All'. Individually refreshing each query manually does not seem to trigger the memory leak (although I haven't manually refreshed for more than 5 minutes every 10 seconds manually).

If I stop the automated data refresh and just leave Excel sit open, after some time (possibly 10-30 minutes) it seems like some form of memory management / garbage collection does occur, as the memory utilisation will eventually start dropping.

Just wondering if there are any other tips or ideas that I haven't thought of, or is there genuinely a memory management issue when refreshing Power Query connections simultaneously?

Ehren (MSFT) 1,781 Reputation points Microsoft Employee

2021-06-04T20:27:00.34+00:00

Thanks for reporting this. I've reached out to the Excel team about it.
Chris Pala 16 Reputation points

2021-10-29T00:42:19.393+00:00

I found this after searching for reasons why this was happening with a sheet we've been using for some time.

In our case I am initiating a refresh of several connections simultaneously through VBA (the VBA for the refresh is manually triggered a button so slightly different to OP).
Excel then starts to consume available all available memory until it eventually crashes. The issue only started 6 months or so ago.

Like OP, the issue is there with both 32-bit and 64-bit Excel. It doesn't happen if the refreshes are triggered one-by-one, or when using the "Refresh All" function on the sheet.
siers 16 Reputation points

2022-03-22T08:41:39.97+00:00

I've done some more testing to try and find a valid workaround.

As mentioned in comments earlier, loading a 'blank' table/query at the end of the VBA query sequence made no major difference to the pattern of slowing increasing memory utilisation.

Next test was using 'Refresh All' in the VBA code instead (i.e. ActiveWorkbook.RefreshAll) of calling each query individually. This turned out to be the opposite and is the worst offender.

Last round was testing multiple vs single queries, and whether that query content does have anything to do with the memory leak.

A single query to a datasource resulted in less memory leakage, but it still experienced an upward trend of memory utilisation.
A single query to a blank table eventually flattened out and stopped it's upward trend.
Also having the 'Fastload' option enabled or not made no difference.

So whilst no workaround yet, comparing all the results so far shows 'Refresh All' should be avoided entirely.

1 answer

Bones 1 Reputation point

2022-02-05T11:00:15.823+00:00

Hi everybody,

Two days ago, I got exactly the same problem after a Win 10 update. I update once in every month. I couldn't fix it within Excel.
The problem exists on different PC.
PC1: Win 10 Pro version: 21H2 build: 19044.1503 experience pack: 120.2212.4170.0 and Excel with MSO 365 (2201 build 16.0.14827.20158) 64 bites
PC2: Win 10 Home version: 21H2 build: 19044.1466 experience pack: 120.2212.3920.0
I have to remove the updates I will see what happens...
Please sign in to rate this answer.
siers 16 Reputation points

2022-03-15T00:13:58.757+00:00

@Bones - Just curious how you went and see if removing the updates made any difference?

Chris Pala 16 Reputation points

2022-03-15T03:24:46.32+00:00

@siers I found a workaround posted elsewhere which seems to have helped in my case. I haven't seen a crash since and I didn't remove any updates.

Load a totally blank, 1 column x 1 row table into powerquery (I called mine ZZZ) and trigger this to refresh as the final step in your query. Good luck!

siers 16 Reputation points

2022-03-17T08:47:42.52+00:00

@Chris Pala - thanks for the suggestion. I've tried out what you've mentioned adding a blank table and running the refresh as the last step.

I've re-ran my test setup that I used for the above over a one hour period with some interesting results:

The overall memory utilisation still did increase but quite a bit slower, and there now appears to be evidence of some form of memory clearing happening every so often (as indicated by the saw tooth pattern).

Will need to re-run my test without the blank table (to see if the blank table definitely results in less memory utilisation, or it's just because of new Office updates or other environmental variables).

Chris Pala 16 Reputation points

2022-03-17T08:54:04.413+00:00

You're doing a great service to the community by testing this so thoroughly @siers . I did not and I couldn't say for certain if this "workaround" is really any kind of "fix" at all. It's entirely possible that a fresh batch of office updates corrected the issue.

Count me as interested to see what your results are testing without the blank table. It's particularly strange that this only seems to happen when refreshing the queries via VBA.

siers 16 Reputation points

2022-03-18T00:44:15.33+00:00

Ok so the blank table made no difference.

The no blank table/query run (in orange) showed the same behaviour and trend.

I left it running for longer, and the trend continues upwards - just a lot slower than the first test I did back in May 2021.

Bones 1 Reputation point

2022-03-21T10:47:26.913+00:00

Removing the updates dosen't work... After a clean install problem is not solved, I thought that point Excel update causes the fault... I bulit up the workbook form the ground twice. Firstly seems to be fine, it ran many hours without memory increasing, but after I close and reopen the workbook the issue reappered. I also found that, if I save the .xlsm as .xlsx, and than copy paste the macro code and save as .xlsm again, the problem disappeared until the first close. I wonder how and why but this was the only solution that worked every time. I made several another modification at the code level (For example I completely deleted a CreateObject("Scripting.FileSystemObject") method from the code) and also rebuilt the querys (for example datamodells are not in use now), but the problem stayed in place. Just the save as .xlsx and save as .xlsm "manual therapy" helped me. After this I don't close the workbook for weeks, It run 0-24. Last weekend Win 10 made a new update, now with the Win 10 Pro 21H2 Build: 19044.1586 experience pack: 120.2212.4170.0 everthing works fine. Which is interesting, because I don't made any changes, but now I can leave the "manual therapy" session. I can't able to decide, what is caused the problem, and what was the real solution... I hope It dosen't come back...

Guy Benbenisti 6 Reputation points Microsoft Employee

2022-05-25T08:00:48.377+00:00

Thanks for being so engaged on this. I'm taking a look into this as part of the engineering team.
However, even with all the details you shared, I could not reproduce the problem and get my memory to increase like that.

In order to take a better look into this, it will be beneficial if you could share a workbook that is causing this problem.
If you don't want to share your workbook due to private data or any other reason, you can also send feedback directly from the product, which will create an issue we can track with more details.
In addition, sharing your Session ID (which can be found under File->Account->About Excel) will also help us investigate more thoroughly.

Thanks.

siers 16 Reputation points

2022-05-26T02:06:11.787+00:00

Hi @Guy Benbenisti ,

Happy to share the workbook I created specifically to test this out - it has public access data so nothing private/confidential.

I tried to share the workbook here, but the attachments don't allow XLS/XLSM files, so I've uploaded to Google Drive https://drive.google.com/file/d/1oDhs_vekbcoxDUb3tSOnBmESes451Kp-/view?usp=sharing

In the workbook:

there's 5 sheets (Query1 to Query5), each with a single query loaded to table.

there's a 'Log' sheet that records the memory usage (from the Macro)

in the Macros, there's 'RefreshAll' that can be run to perform a refresh of all the 5 queries, write the memory/date/time to the Log sheet, then run again in 5 seconds time

Session ID is currently F6024112-9B87-4C1B-AEEA-666CD03BD0E8

Thanks!

Ramya Patki 1 Reputation point

2022-09-07T19:43:39.857+00:00

@Chris Pala : I AM NOT SURE WHAT YOU MEAN WHEN YOU SAY "trigger this to refresh as the final step in your query." Can you pls post an example ?

siers 16 Reputation points

2022-10-11T07:41:57.243+00:00

Hi @Guy Benbenisti ,

Just wondering if there's been any update or progress in investigating this,, or whether you need any other information or testing to be done.

Thanks!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Power Query memory increasing when data connections refreshed simultaneously (manually or via VBA)

1 answer

Your answer