Fix Excel Pivot Table Connected to Power BI Dataset Overwriting Row Values
Question details
The user needs to restore original dataset row labels in an Excel Pivot Table connected to a Power BI dataset after they were manually overwritten.
- Product
- Microsoft Excel, Power BI
- Device & OS
- not provided
- Scenario
- Restoring the original row label values in a Pivot Table connected to a Power BI external dataset without completely rebuilding the connection.
- Observed behavior
- Standard methods like removing and reapplying fields do not restore the overwritten row labels, even though slicers and CUBEMEMBER formulas continue to display the correct original values.
Before proceeding, ensure that your Power BI dataset connection is active, your credentials are up to date, and you have saved a copy of your workbook.
Clear Custom Labels and Refresh the Pivot Table Cache
Resetting the Pivot Table cache and clearing the customized cells can force Excel to pull the original labels from the Power BI dataset.
Excel saves custom labels in a hidden Pivot Table cache to boost performance. When connected to complex external data models like Power BI, this cache can aggressively hold onto manual overwrites even after the field is temporarily removed.
Click directly on the row label cell in the Pivot Table that has been overwritten and press the Delete key. In some Excel versions, this reverts the label to its default.
Right-click anywhere inside the Pivot Table and select 'PivotTable Options' from the context menu.
Navigate to the 'Data' tab in the Options dialog box. Find the 'Number of items to retain per field' dropdown and change it from 'Automatic' to 'None', then click OK.
Go to the 'Data' tab on the main Excel ribbon and click 'Refresh All' to force a fresh data pull from the Power BI dataset.
Uncheck the problematic field in the PivotTable Fields pane, then check it again to rebuild the rows.
Edit CUBEMEMBER Formula Captions
If the Pivot Table was converted to OLAP formulas, manually adjusting the CUBEMEMBER formula removes the custom overwrite.
Try WPS Office for Smooth and Lightweight Data Analysis
Dealing with complex external data cache issues can be frustrating. If you need a lightweight, fast, and highly compatible spreadsheet tool for everyday data analysis and Pivot Tables, consider trying WPS Spreadsheet.
- 1. Download and install: Visit the official WPS website to download and install WPS Office on your computer.
- 2. Open your spreadsheet: Launch WPS Spreadsheet and easily open your existing .xlsx files.
- 3. Manage your data: Use the familiar 'Insert' tab to generate Pivot Tables and analyze your local datasets instantly.
Frequently Asked Questions
Why do custom row labels persist in Excel Pivot Tables?
Excel caches Pivot Table data—including manually typed custom row labels—to optimize performance. This cache will sometimes remember custom names even if you drag the field out of the layout and bring it back, especially with external OLAP data models.
How do I completely reset my Pivot Table cache?
To reset the cache, right-click the Pivot Table, select 'PivotTable Options', go to the 'Data' tab, change 'Number of items to retain per field' to 'None', click OK, and then click 'Refresh' on the Data ribbon.
Can I use slicers to fix overwritten values?
No. While slicers will accurately display the unmodified dataset values from Power BI, interacting with them will only filter the data. It will not force the Pivot Table rows to drop their customized display names.
