legalhustler
Well-known Member
- Joined
- Jun 5, 2014
- Messages
- 1,171
- Office Version
- 365
- Platform
- 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!
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!
