The following VBA Macro will change the connection properties of all the Power Query connections to enable or disable background refresh.
'Description: Enable or disable background refresh on all Power Query connections
'Author: Jon Acampora, Excel Campus
Dim lCnt As Long
'The following code loops through all connections
'in the active workbook. Change the property to
'True to Enable, False to Disable background refresh.
For lCnt = 1 To .Connections.Count
'Excludes PowerPivot and other connections
If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
.Connections(lCnt).OLEDBConnection.BackgroundQuery = False
Disabling background refresh allows the query to complete the refresh before the other queries are refreshed. It also allows all queries to be refreshed before the pivot tables are refreshed. This is important if your workbook contains pivot tables that use a Power Query output table as the source data range.
When clicking the Refresh All button on the Data tab, we typically first want to refresh the queries (Tables) before refreshing the pivot tables. Disabling the background refresh of the connections on the Connection properties window makes this possible.
Refresh All Queries and Then Refresh All Pivot Tables
The macro above only needs to be run once to change the properties of the connections. The properties will be saved and stored with the workbook.
When pressing the Refresh All button or running the following line of code, the Power Query connections (Tables) will be refreshed before the pivot tables are refreshed in the workbook.
This allows you or the user to refresh all the queries and pivot table reports with the press of a button.