Power Query Performance Issues / Crashing for Small Data

[Parky4002]

I have an issue where excel is taking a long time to refresh simple queries and often crashing.





Background

1. I have a number of files querying and transforming source data - all of these queries work fine and run in <10-20 seconds.
2. Source data is all stored as CSVs with queries transforming and merging tables
3. I have a separate file which then consolidates all of the query outputs (around 5-6 tables) using power query into a master file, with each query having no more than 1-2 simple steps (i.e. promote headers & change type)
4. In the Master file I have a master table performing a number of volatile calculations, namely lookups (note that calculations are manual) against the data in the queries (which are all loaded as tables)
5. All files stored on sharepoint
6. Number of records in the queries range from small 100s to c. 20k.
7. None of the queries are dependent on one another - only the source data.

Issue

1. A number of queries take a long time to refresh and ultimately crash excel (even when fetching only c. 100-400 new records If I leave it running for c.10-15 mins it sometimes refreshes). Points to note:
   1. Query editor works fine and pulls in the new information in.
   2. Each query has almost no steps in it - outside of promote to headers and perhaps 1 change type.
   3. Issue only observed on the query refreshes - it simply doesn't load to the table
   4. If I re-build the query, all of the data is pulled in seconds
   5. Running the calculate now function doesn't take a great deal of time - again seconds.

Attempted resolutions:

• Between points 2 & 3 of background I've saved the new queries info as CSVs to remove any dependency on prior queries into the source
• Background refresh is turned off
• Cleared query cache
• Cleared excel cache
• Cleared privacy settings

Is there any advice to improve performance (I'd expect the c. 20k rows to take <30-60 seconds, not >15mins & often crash).



With the calculate function operating quickly I can't see it's that causing the issue for the query to load to a table, but appreciate that may well be something to look at (although id only look to do this if all other options are exhausted).



Thanks in advance.

 

[Parky4002]

When you say there are many formulas... Does the PQ table contain added columns with formulas?

Then why do you do that? Chances are you can replace them with PQ code.

But it can explain why loading is slow.

Turn auto calculate off, do the refresh and recalculate. To test the hypotheses.

So the formulas use the output of the queries and no queries are dependant on the table with formulae.

The complex nature of the formulae would be difficult to replicate with ease in PQ (without coding, which would probably take a while to get right). Something like access would be better suited using build form my skill set.

That being said, calculations are turned to manual already - and I don’t believe those to be the issues (if I copy the query to a new work book I observe the same issue after the initial copy). The calculations also calculate less than 10s.

The only workaround solution I’ve found at the moment is to copy or duplicate the query - which performances a refresh and load to successfully within seconds - and then change and references using find and replace on a single row, ultimately copying down formulas for the remainder of the table (to avoid any excessive operation time).

Whilst the workaround is annngoing, it is palatable but would love if someone had a similar issue and found a real solution.

Thanks again for the help

 

[GraH]

It's a mystery to me why you use web.contents for a connection on SharePoint.
You might want to use the function written by Imke Feldmann. You can download it on her website
Biccountant. Improve File Import from SharePoint in Power BI and Power Query –

 

[Parky4002]

That’s what was auto generated when using get data from file with the file paths copied in.

 

[GraH]

That’s what was auto generated when using get data from file with the file paths copied in.

Maybe try to use From File -> From SharePointFolder (if possible) to see if that's an improvement. You would only have 1 query to connect to the source. I guess, if you've used a connection per file you connect for each file again and again to the same source. That can explain what you notice.
Yet I notice you have version 2013, so that might also explain why things are different in comparison with my version. Maybe in 2013-version that connector to SharePoint did not exists, but it would surprise me it was not yet available. The API version of the connection might be outdated and be the cause of the query lagging.