Power Query Out of Memory & Performance Issues

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,214
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm using Excel Power Query on Office 365 that connects to a few SharePoint lists/tables and to a work shared network drive. My queries were easy to build and the performance was good at the beginning but as I started connecting to more SharePoint lists and to files on the network drive it's been a nightmare. ANY changes I make now to my queries I get a Out of Memory message prompted. I click it a few times to close it then quickly hit the Save button. Then reopen my work, make a change and repeat the process....IT'S PAINFUL.

My computer specs: Windows 10 Enterprise, 64 bit operating system, 16GB RAM and Solid State Drive (SSD)

My file size: less than 1 MB, which is pretty darn small.

There are 16 queries that gets loaded to the workbook and about 36 queries that are set to Connection Only. The 16 child queries are dependent on the 36 parent queries. When I click Refresh All button on the Data tab only some of the 16 queries load and the others have a spinning wheel then the Out of Memory message pops. I save the workbook reopen it and manually click the gear icon to the right of the query in the Queries & Connections pane for each query and that Out of Memory message shows.

I have fixed some settings like turned off "Never all data previews to download in the background" and "Always Ignore Privacy Level settings." I have also wrapped Table.Buffer function around some of my 36 parent queries.

I also avoided referencing queries in Excel Power Query b/c I read it tremendously flags your refresh time. See: Ken Puls Explains Why Excel's Power Query Refresh Speeds Suck

It would have been nice to reference my queries because if I make any change I have to manually do it to 3 other parent queries that are essentially similar code.

Does Excel Power Query on Office 365 have performance issues connecting to SharePoint lists/table and network drives? Is this also the case with Power BI Desktop? Any workarounds, tips? Will adding the tables to the Data Model/Power Pivot improve my performance and refresh? I hate to use separate workbooks and divide my queries so I like to keep them consolidated.

Found this article but I think it's referring to Power BI not Excel Power Query: Power BI and SharePoint – Terrible Together

Any help/advise is appreciated!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Is your office also 64 bit it is it like in many installation still 32 bit?
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top