Fix Power Query Data Model Refresh Hanging in Excel 2016
Question details
The user experiences an infinite loading or hanging issue when refreshing a Power Query that loads into the Excel data model.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Attempting to refresh a Power Query that reads the current workbook, manipulates data, and loads it into the data model.
- Observed behavior
- The refresh process hangs indefinitely and never finishes when the data model load is enabled.
Save a copy of your workbook before making changes to query settings, and ensure your Excel application is not actively running other intensive background macros.
Disable the 'Load to Data Model' Option
Bypassing the data model is the most stable workaround for queries hanging during refresh in Excel 2016.
In many Excel 2016 builds, queries that read from the current workbook and load directly back into the Data Model can cause a circular evaluation loop or trigger a known bug that halts the refresh. Disabling the data model load isolates the issue.
Go to the 'Data' tab on the Excel ribbon and click 'Queries & Connections' to open the side pane.
Right-click the problematic query in the pane and select 'Load To...'.
In the Import Data dialog box, uncheck the box labeled 'Add this data to the Data Model'.
Choose 'Table' to load the data directly into a worksheet, or 'Only Create Connection' if it is just an intermediate query.
Click 'OK' to save the settings, then click 'Refresh All' to test if the query completes successfully.
Install the Latest Office Updates
Updating your Office installation may resolve the underlying software defect causing the data model to hang.
Try WPS Office for Fast and Reliable Data Processing
If Excel 2016 continues to hang during complex queries, consider using WPS Office. It provides a lightweight, highly compatible alternative for handling spreadsheets, filtering data, and managing large datasets without heavy performance overhead.
- 1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsx file.
- 3. Process Data Without Hanging: Use WPS Spreadsheet's built-in data tools to filter, analyze, and manage your data efficiently.
Frequently Asked Questions
Why does my Power Query get stuck on 'Loading data model'?
This is often caused by a known software bug in Excel 2016, circular dependencies when queries read from the current workbook, or memory limitations when processing complex transformations before loading.
Can I still use my data if I don't load it to the Data Model?
Yes. You can choose to 'Load to Table' to place the data directly into an Excel worksheet, or 'Only Create Connection' if the query is merely an intermediate step used by other queries.
How do I check my current version of Excel 2016?
Go to File > Account in Excel. Look under the 'About Excel' section to see your exact version and build number, which can help determine if you are missing critical Power Query updates.
