Power Query Loading in Excel Very Slow

[bingsu]

I have a workbook that uses multiple queries, with lots of merges/joins. I know that this can slow down the data processing time a bit, however I am getting extremely slow query load times, not due to the transformations but simply loading it into an excel workbook. 

One of the datasets I am working with only has 238 rows, and when a refresh my Excel Workbook's data, it just shows 238 rows loaded for incredibly long without actually updating the worksheet. The query previews in query editor load relatively quickly, so I assume this is an Excel issue. Any tips would be greatly appreciated.

[ahadkarimi]

Hi @bingsu, right-click the query in the Queries pane -> choose Properties, and then deselect the option for Enable Background Refresh.

[bingsu]

Unfortunately this did not really affect the performance

[Anonymous]

What's your data source, and can we see your M code? As a general statement, joins aren't necessarily bad, and you can almost always get them to fold, but cross database joins can be slow, and bad indexing can make joins slow. But there is ALWAYS a way to speed it up when you know your Power Query.

--Nate

[bingsu]

Due to security reasons I cannot share my M-Code. However, I feel like its less of an issue with my queries, and more so of an issue loading it into Excel. My query previews load quickly, but my Excel basically freezes after trying to refresh my Query Data and load it into a worksheet. 

In my worksheet, I already have data from a previous refresh, and refreshes may change the size of the table. Could this be an issue?

[Anonymous]

Hi @bingsu ,

The time required to load in the query preview is not equal to the time required for your query to run completely. The query preview only uses a small number of samples to test your query to return the expected query results. It does not fully execute the entire query. So it's possible that the problem still lies in the complexity of your query.
My suggestion is that you can use the same data source in a new excel and make the same query, then delete some of the queries you made and load them into excel to see if there is a significant reduction in the time required to load into excel. If there is a significant reduction, then it should be a problem with the complexity of your query.
Or you can try to advance all the operations you need to be done in the data source, and then use Power Query to connect to the processed data source and load it directly into excel, having confirmed how much time it takes for excel to load so much data.

If after testing you can determine that slow loading is not a query problem but an excel problem, then perhaps you can go to excel or office related forums to seek more professional help.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.