Your Power Query preview pane shows old data even after you refresh the source. This happens because Power Query caches preview data to improve performance. The cached data can become outdated or corrupted, preventing the preview from updating. This article explains how to clear the Power Query cache to force a fresh preview.
Key Takeaways: Clearing the Power Query Data Cache
- Data > Queries & Connections > Right-click query > Refresh: This is the standard refresh that may not clear the preview cache.
- Power Query Editor > File > Options and Settings > Query Options > Data Load > Clear Cache: The primary method to delete all cached preview data for the workbook.
- Closing and reopening the Excel workbook: A simple action that clears the in-memory cache for the current session.
Why Power Query Caches Preview Data
Power Query stores a local copy of data from your last preview operation. This cache allows the Power Query Editor to display previews instantly without querying the source every time you click a step. It is a performance feature for designing queries.
The cache is separate from the data loaded into your Excel worksheet. Refreshing the query in the worksheet loads new data into cells but does not always clear the editor’s preview cache. Problems occur when the source data structure changes, like renamed columns, or when the cache file becomes corrupted. In these cases, the preview pane continues to show the old, cached metadata and sample rows.
Types of Cache in Power Query
Power Query uses two main caches. The preview cache is stored in your system’s temporary files and is used only within the Power Query Editor window. The data model cache is used by the Excel Data Model for PivotTables and resides within the workbook file. Clearing the preview cache does not affect the data already loaded into your report.
Steps to Clear the Power Query Data Cache
Follow these steps to delete the cached preview data and force Power Query to fetch fresh data from the source.
- Open the Power Query Editor
In Excel, go to Data > Get Data > Launch Power Query Editor. Alternatively, find your query under Data > Queries & Connections, right-click it, and select Edit. - Access Query Options
Inside the Power Query Editor, click File on the ribbon. Then select Options and Settings > Query Options. - Navigate to the Data Load Settings
In the Query Options dialog box, select Data Load from the left-hand pane. This section contains global settings for how Power Query handles data. - Clear the Cache
On the right, locate the Cache section. Click the Clear Cache button. A confirmation message will appear. Click OK to proceed. This action deletes all cached preview data for all queries in the current workbook. - Close and Reopen the Editor
Close the Query Options dialog. Then, close the Power Query Editor window. Reopen the editor by editing your query again. The preview will now run a fresh query against your data source.
Alternative Method: Using the Diagnostic Window
For advanced users, you can use a hidden diagnostic pane to clear cache more granularly.
- Open the Diagnostic Window
In the Power Query Editor, press Ctrl+Shift on your keyboard and click the Diagnostics option under the File tab. This opens a hidden window. - Clear Specific Caches
In the Diagnostics window, go to the Cache tab. Here you can see cached entries. You can select specific entries to clear or use the Clear All button to purge everything.
If the Preview Still Does Not Refresh
Source File is Open or Locked
If your query connects to a local file like an Excel workbook or CSV, ensure the source file is not open in another program. An open file can lock the data, preventing Power Query from reading the updated content. Close the source file in all other applications and try refreshing the preview again.
Query Steps Reference Deleted Columns
Clearing the cache forces a new read of the source. If the source table no longer has a column named in one of your query steps, the preview will fail with an error. You must edit the query to remove or rename the step referencing the missing column. Look for a step that uses Remove Columns, Rename Columns, or a calculated column that references an old column name.
Network or Database Credentials Have Expired
For database or online source queries, your cached credentials might have expired. After clearing the cache, Power Query may prompt you to re-enter your login details. Check the Data Source Settings under File > Options and Settings to manage your saved permissions.
Standard Refresh vs. Clearing Cache: Key Differences
| Item | Standard Query Refresh (Data > Refresh) | Clearing Power Query Cache (Query Options) |
|---|---|---|
| Primary Action | Re-runs the query to update data loaded into the Excel worksheet | Deletes temporary preview data used inside the Power Query Editor |
| Effect on Worksheet | Updates tables, PivotTables, and the Data Model with new data | No direct effect on worksheet data |
| Effect on Preview Pane | May not update if cached metadata is stale | Forces preview to fetch fresh metadata and sample rows from source |
| Location | Excel main ribbon under the Data tab | Power Query Editor under File > Options and Settings |
| Use Case | Updating reports with the latest source data | Fixing preview errors after source schema changes |
You can now clear the Power Query data cache to resolve a stuck preview. Use the Clear Cache button in Query Options when your source data structure changes. For persistent issues, check your query steps for references to old column names. Try using the diagnostic window for more control over specific cached entries.