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.

 

[johnny51981]

Are you able to test it out by placing your CSV source files locally on your machine and pointing it there?

It may be a hiccup in your internet/network connection.

 

[Parky4002]

Are you able to test it out by placing your CSV source files locally on your machine and pointing it there?

It may be a hiccup in your internet/network connection.

That doesn’t look like it’s resolved the issue after testing.

The query is still taking considerable amount of time to load when refreshed.

 

[GraH]

Did you remove empty rows?
Might be a longshot...
How do you connect to Sharepoint?
If you can share the query statements, we might be more precise in helping you.

 

[Parky4002]

I’ve not tried remove empty rows so will try that.

If I duplicate the query the duplicate refreshes and loads all new data immediately (performing as expected) yet hitting refresh in the original still causes issues. If I try to refresh the duplicate tomorrow it will then result in the same issue.

Very strange.

Thanks again for the suggestions.

 

[GraH]

If a duplicate works fine, remove the original and test the refresh. Never heard of a query going "corrupt" though.
You probably have SharePoint.Files as connector in the Source step. Change it to SharePoint.Contents. You will then have the possibilities to filter on libraries and load less files to start with. It might help.

 

[Parky4002]

If a duplicate works fine, remove the original and test the refresh. Never heard of a query going "corrupt" though.
You probably have SharePoint.Files as connector in the Source step. Change it to SharePoint.Contents. You will then have the possibilities to filter on libraries and load less files to start with. It might help.

Thanks I’ll give that a go.

The duplicates have the same issues though after the initial data pull / load to.

 

[GraH]

Are the source files .xlsb? Those are not best friends for Power Query.

 

[Parky4002]

Thanks for the suggestions.

The connector being used is web.contents - so I’ve tried changing and refreshing but have the same issue.

The source files are all csv

Really strange as copying the queue loads instantly with no issues but refresh gets stuck at the load to step it seems…

 

[GraH]

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.