Remove Cached Data from an Excel Workbook Before Saving to Dataverse with WPS Spreadsheets
Question details
The user needs to save a customized Excel workbook to Dataverse while removing cached data from a Power BI connection, queries, PivotTables, and the data model.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user customized an Excel workbook with Power BI-connected queries, PivotTables, and a data model, and now needs to save a clean version to Dataverse without embedded cached results.
- Observed behavior
- The workbook retains cached query, PivotTable, and data-model results after loading Power BI data, so saving it directly to Dataverse would keep unwanted cached data.
Open the workbook in WPS Spreadsheets and make a working copy. Then check the Data tab for existing connections and unhide any sheets that contain imported table or data-model ranges so they can be removed before saving.
Clear Workbook Connections, PivotTable Caches, and Data-Model Ranges in WPS Spreadsheets
Use this manual cleanup method for a one-off workbook when you need to keep the customized layout but remove all cached Power BI and query results before uploading to Dataverse.
Manual cleanup is the most direct way to remove visible data and cached objects while keeping the customized workbook structure. It works best when the workbook does not need to be refreshed from the original connection again.
In WPS Spreadsheets, open the customized Excel workbook and click the Data tab.
On the Data tab, open Connections or Existing Connections, select the Power BI/query connection, and click Remove or Delete. Confirm the removal when prompted.
Select each PivotTable, go to the PivotTable Analyze tab, choose Select > Entire PivotTable, and press Delete. If you need the summary values, copy the visible values and paste them as values first.
Right-click any hidden worksheet tab, click Unhide, select the imported table or data-model range, and press Delete. Delete or re-hide the sheet as needed.
Click Menu > Save As, choose Excel Workbook (*.xlsx), save the file with a new name, and close the workbook before uploading it to Dataverse.
Automate Repeated Cache Removal with VBA or Open XML SDK Before Dataverse Upload
Use this approach when you need to clean many workbooks or repeat the same cleanup task and must strip connection metadata programmatically.
Save a Clean Excel Workbook to Dataverse with WPS Spreadsheets
Open your customized Excel workbook in WPS Spreadsheets, remove Power BI query connections, PivotTable caches, and data-model ranges, then save a clean .xlsx copy ready for Dataverse.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the customized Excel workbook that contains the Power BI connection.
- 2. Remove cached connections and PivotTables: Use the Data tab to delete query connections, delete or clear PivotTables, and remove hidden data-model sheet ranges.
- 3. Save as a clean .xlsx file: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save the clean copy under a new file name.
- 4. Upload the cleaned workbook to Dataverse: Use your Dataverse import form or Power Apps flow to upload the cleaned workbook.
Frequently Asked Questions
Why does my Excel workbook still contain Power BI data after I refresh it?
Power BI query results and data-model caches are stored inside the workbook. In WPS Spreadsheets, open the Data tab, delete the existing connections, and clear or delete PivotTables and imported table ranges before saving.
Can I remove Power BI connection metadata in WPS Spreadsheets?
WPS Spreadsheets can delete many workbook-level query connections and cached table or PivotTable areas. Advanced Power BI model metadata may require Microsoft Excel, VBA, or Open XML SDK, so test the cleaned file before uploading to Dataverse.
Will saving as .xlsx from WPS Spreadsheets remove all cached data automatically?
No. Saving as .xlsx only changes the file format. You must manually remove connections, PivotTables, and hidden data-model ranges or run a cleanup script, then save a new clean copy.
Do I have to delete my PivotTables, or can I keep the values?
If you need the summary values, copy the PivotTable cells and paste them as values before deleting the PivotTable. This keeps the visible data without the underlying cache.
What should I check before uploading the workbook to Dataverse?
In WPS Spreadsheets, open the Data tab and confirm no existing connections remain, check for hidden sheets with imported tables, and save a test copy to verify the file opens without refreshing external data.
