Need: Delay Excel Calculations Until Power Query Finishes It's Updates

[Sting_NC_USA]

Hello all, long-timer viewer and fan of Mr. Excel, and I've finally taken the leap to join the forum!

Jumping right in, here's a scenario I'd appreciate your help in solving...

I have a highly, formula intense workbook (in Office 2016) with Dashboards and Tracking Data. The workbook is tied to multiple Power Query instances and refreshes upon opening. What's happening is that Excel will run the first query, recalculate, run query two, recalculate, and so forth. There's a ton of unnecessary re-calculating going on (2.5 minutes worth), and I'd like to find a way to halt calculations until each of the queries are complete with their updates.

I've already unchecked the "Enable Background Refresh" option in the Connection Properties window, though I'm at a loss as to how to make this work. I've searched here, Google searched and haven't found anything yet.

Any ideas? Thanks!

 

[sandy666]

un-check all Refreshes and do that manually
or your queries are not optimised well

 

[Sting_NC_USA]

Thanks Sandy, I need the document to refresh, and the queries do run quickly. The issue is that excel recalculates everything in between each individual query. I'm looking to delay the recalculation portion until all queries have run, so that the calculations only run once. Another way of saying it: It would be great if autocalc could momentarily be turned off, then all queries run, then autocalc turns back on.

Hope that helps to clarify the issue.

 

[sandy666]

so you will need vba to define which one (connection) you want to refresh right now and then refresh all but vba is not my story, sorry
each query is from different tables or some using the same table?

 

[Sting_NC_USA]

Thanks. My short-term solution is to disable all refresh items, attache a macro that disables formula calculations, runs each query one at a time, then turns formula calculations one time. I'm just trying to simplify the user experience. Have a great weekend!

 

[sandy666]

I saw what you read, maybe it will help
Good luck
Have a nice day

btw. if you turn off all refreshes in connections and will use right click on result table or pivot it will be the same what you said above but without vba, it will refresh selected table/pivot only