cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JeffWeir Regular Visitor
Regular Visitor

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

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
ImkeF
MVP

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

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.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

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

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ImkeF
MVP

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

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.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,798)