How to Stop Power Query from Refreshing Dependent Queries in Excel
Question details
The user is experiencing an issue where dependent loaded queries automatically refresh after editing an unloaded Power Query.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Editing an unloaded staging query and selecting Close & Load to apply changes.
- Observed behavior
- Dependent loaded queries are automatically triggered to refresh, which is a behavioral change introduced in Current Channel build 2602 compared to build 2601.
Verify your current Office update channel and build version, and save a backup copy of your workbook before adjusting global data load settings.
Disable Background Refresh and Parallel Loading
Modifying how Excel handles query loading and background processes can prevent dependent queries from refreshing unexpectedly.
By turning off parallel loading globally and disabling background refresh for specific queries, you can regain control over when your data updates.
In Excel, navigate to File > Options > Data. Locate the 'Data Load' section and uncheck the option for parallel loading, then click OK to apply the changes.
Open the Queries & Connections pane. Right-click on the dependent query, select Properties, and uncheck 'Enable background refresh' under the Usage tab.
Keep Staging Queries as Connection Only
Ensure intermediate queries are not loaded to the worksheet to minimize unnecessary downstream refreshes.
Test Another Office Update Channel
Since this issue was absent in build 2601, changing your update channel can bypass the bug until a patch is released.
Try WPS Office for a Stable Data Analysis Experience
If frequent bugs or regressions in Microsoft Office disrupt your workflow, consider switching to WPS Office. It provides a stable, lightweight, and highly compatible spreadsheet tool for your daily data management tasks without the overhead of unpredictable updates.
- 1. Download WPS Office: Visit the official WPS website to download and install the free version of WPS Office.
- 2. Open WPS Spreadsheet: Launch the application and select 'Spreadsheet' from the home screen.
- 3. Load your workbook: Click File > Open and select your existing Excel workbook to continue working seamlessly.
Frequently Asked Questions
Why do my dependent queries refresh automatically in Excel?
In Excel build 2602 (Current Channel), a behavioral change or regression causes dependent loaded queries to refresh automatically when an unloaded staging query is edited and saved using Close & Load.
How can I stop background refresh in Power Query?
You can stop background refresh by right-clicking your query in the Queries & Connections pane, selecting Properties, and unchecking the 'Enable background refresh' option under the Usage tab.
What does 'Connection Only' mean in Power Query?
'Connection Only' means the query processes the data and keeps it in memory for other queries to reference, but it does not load the final output into a physical Excel worksheet or the Data Model.
