Out of Memory Error | Power Query | Excel Forum

I have had Out of Memory errors as well on one of my Excel projects. It is a reporter module for the partners of a law firm and it has Power Query programmatically pull in various data files (txt or csv files) and process them into various worksheets. One query connects to a folder which holds lawyer timesheets in csv format (each one is only about 1kb) but they will build up in number to be thousands over time. Still, I wouldn't think that this should be a problem as Power Query is meant to handle large amounts of data. (I could reduce the total number of timesheet files to only by programmatically combining them before running the query but the total amount of data would be the same and I don't think it should make a difference).

Here are some things I tried which you may find useful.

1. Break larger queries into smaller queries: the timesheet query above performs a number of transforming steps and actually pulls in files from a number of different folders. I originally had one big query but broke this down into smaller queries being 'connections only' and then have a final master query (which loads to a worksheet) which feeds off the step queries. The step queries are named sequentially (e.g. TimeSheetStep01, TimeSheetStep02 etc) so that when run programmatically by VBA, they process in the correct order.
2. Background Data Setting disabled: if you go to lesson 5.03 "Load Settings & Automatic Refresh" (minute 1:25) of the Power Query course, the Query Options in Excel 2016 have been expanded. Under "CURRENT WORKBOOK", there is a "Background Data" category. Under this I have disabled the "Allow data preview to download in the background". (see this thread  ). This seems to stop the issue with the Microsoft MashUp Container consuming too much RAM. NOTE: this setting only applies to the current workbook so you have to set this for each workbook.
3. I have disabled "Background Refresh" and "Refresh this Connection on Refresh All" on all of my queries. On the Data menu tab, select "Connections" (near Refresh All) and for query, edit the Properties. You need to do this one by one. What this means is that my queries will only be updated by VBA when opening the workbook or when running the RefreshQueries macro. Disabling the "Background Refresh" on each query should have Excel process one query at a time before moving to the next query (if I understand correctly) and also prevent the user using Excel until the query refreshes are finished. Disabling "Background Refresh" also means that queries are refreshed before pivot caches as these may be linked to connected table.
4. Only allow refresh by vba code and, following from 3 above, disable "Refresh every x minutes" and "Refresh data when opening the file" because I provide the user with a macro to refresh the data. The refresh macro disables Excel until the queries are refreshed but loads a splash screen which shows a progress bar (and the names of the queries as they are processed) so that the user knows that something is happening.
5.  The code for refreshing the queries is something like this...

540 For Each con In ThisWorkbook.Connections

620 If Left(con.Name, 8) = "Query - " Then

690 With ThisWorkbook.Connections(Cname).OLEDBConnection
700 On Error Resume Next
710 .EnableRefresh = True
720 On Error GoTo myRefreshQuery_Error

750 .Refresh

760 Application.CalculateUntilAsyncQueriesDone

830 End If
NextConnection:
840 Next

Note for line 620 I'm using Excel 2016. From what I can see earlier versions may prefix the query name with "Power Query - " instead of "Query - " from what I can see online but I'm not sure.

I hope this helps. I don't claim to be a Power Query expert and welcome other course members to share their RAM/CPU usage issues/solutions. I'm happy to be corrected on anything I've written above.

Cheers,

Greg