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

Sting_NC_USA

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
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!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,075
un-check all Refreshes and do that manually
or your queries are not optimised well
 
Last edited:

Sting_NC_USA

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,075
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

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,075
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,113,903
Messages
5,544,974
Members
410,647
Latest member
LegenDSlayeR
Top