Powerquery - Returning data as Tables seems to cause memory thrashing beyond certain point?

[JeffWeir]

Hi all. I have an Excel file that has 38 queries in it, with 7 of those returning data to the DataModel, another 7 returning 'exceptions' to Excel as a Table (but not to the DataModel), and the rest being for data staging.  It takes around a minute to refresh all the queries manually one by one. But for some reason, clicking the Refresh All option in the ribbon inexplicably chokes Excel, and the culprit seems to be due to the combined number/load of those 'exceptions' queries that are being returned to Excel as Tables (vs to the DataModel) 

• If I hit Refresh All, Excel says "Retrieving Data" but remains in an unresponsive state until I use End Task to kill the app.
• I changed the 'exceptions' queries to Connection Only (i.e. stopped them being returned to the grid) and then Refresh All only took around a minute.
• I added the 'exceptions' queries back one by one, refreshing each time, and it worked fine until query number 6, at which point it did the "Retrieving Data" unresponive thing mentioned above
• I changed all the 'exceptions' queries to load to the DataModel instead of the grid, and it refreshes just fine

We're not talking massive amounts of data...the largest table has 236,000 rows, the next largest only 100,000 rows, and the others are tiny...particulary the 'exceptions' tables I was returning to the grid (I'm talking hundreds of rows if that).

Anyone know anything about why returning data as a Table would cause this issue, but returning it to the DataModel works just fine?

[v-piga-msft]

Hi @JeffWeir ,

It seems that you have memory issue when refresh queries in query editor?

Do you get any error message? If you have error, could you share the screenshot of the error message?

I think you could describe your scenario with images so that we could understand it better.

In addition, you could refer to this blog which provides the tips for performance in power query.

@ImkeF any other ideas?

Best  Regards,

Cherry

[ImkeF · Accepted solution]

I'v collected some more performance tips on my site here:  https://www.thebiccountant.com/speedperformance-aspects/ 

Although the specific problem @JeffWeir  mentioned is not covered there unfortunately it covers some aspects that should always be checked / tried out when performance problems occurs.

What I would try as a last resort is to keep the problematic query loaded only to the data model, reference it again in the query editor and then load that to the sheet. With any luck, this will trigger a totally different evaluation plan that doesn't have those problems.