SOLVED

Wait until Query is Refreshed

%3CLINGO-SUB%20id%3D%22lingo-sub-209325%22%20slang%3D%22en-US%22%3EWait%20until%20Query%20is%20Refreshed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-209325%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20query%20in%20my%20excel%20which%20is%20directly%20loaded%20to%20a%20Pivot%20Table.%20In%20the%20connection%20properties%20of%20the%20Query%2C%20I%20have%20set%20the%20query%20to%20refresh%20on%20workbook%20open.%20And%20I%20have%20done%20the%20same%20thing%20to%20the%20Pivot%20table.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20know%20and%20customize%20the%20order%20of%20Refresh.%20I%20would%20like%20the%20Query%20to%20be%20refreshed%20first%20and%20then%20the%20pivot%20table.%20How%20to%20achieve%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20I%20did%20not%20load%20the%20query%20to%20a%20sheet%2C%20I%20loaded%20the%20query%20directly%20into%20a%20pivot%20table%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-209325%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-210618%22%20slang%3D%22en-US%22%3ERe%3A%20Wait%20until%20Query%20is%20Refreshed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-210618%22%20slang%3D%22en-US%22%3EIt%20should%20do%2C%20just%20mark%20each%20Pivot%20as%20refresh%20on%20open%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-210353%22%20slang%3D%22en-US%22%3ERe%3A%20Wait%20until%20Query%20is%20Refreshed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-210353%22%20slang%3D%22en-US%22%3EOkay%2C%20I%20didn't%20know%20that.%20That's%20great.%3CBR%20%2F%3EDoes%20it%20work%20even%20if%20I%20have%20multiple%20pivot%20tables%20from%20multiple%20queries%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-210168%22%20slang%3D%22en-US%22%3ERe%3A%20Wait%20until%20Query%20is%20Refreshed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-210168%22%20slang%3D%22en-US%22%3EHi%20Anwesh%2C%3CBR%20%2F%3E%3CBR%20%2F%3EAre%20you%20using%20Power%20Query%20%3F%20%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20set%20the%20Pivot%20to%20refresh%20on%20open%20and%20not%20the%20query%20then%20the%20Pivot%20Table%20will%20trigger%20the%20query%20to%20refresh%20and%20then%20update%20the%20Pivot%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I have a query in my excel which is directly loaded to a Pivot Table. In the connection properties of the Query, I have set the query to refresh on workbook open. And I have done the same thing to the Pivot table. 

I would like to know and customize the order of Refresh. I would like the Query to be refreshed first and then the pivot table. How to achieve this?

 

Note: I did not load the query to a sheet, I loaded the query directly into a pivot table

5 Replies
Highlighted
Best Response confirmed by Anwesh Gangula (Contributor)
Solution
Hi Anwesh,

Are you using Power Query ?

If you set the Pivot to refresh on open and not the query then the Pivot Table will trigger the query to refresh and then update the Pivot
Highlighted
Okay, I didn't know that. That's great.
Does it work even if I have multiple pivot tables from multiple queries?
Highlighted
It should do, just mark each Pivot as refresh on open
Highlighted
I have a same question, Refresh on open not working in Pivot table .The source data is another sheet in the same workbook and the same got updated by power query from external data source. Powerquery updates ,but not pivot .Any idea why it is not updating after enabling the refresh on open for that pivot table?
Highlighted

@Anwesh Gangula and @Anjupjose

I would also suggest going to the properties of the query and uncheck "Enable background refresh" if possible. Depending on the situation, this setting may appear grayed out. But if it isn't, make sure it does NOT have a check mark and git it a try.

 

To go to the properties, you can open the queries pane on the right-hand side in Excel, and then right-click and choose Properties at the bottom of the menu.