Pivot Table Issues

Fix Excel PivotTable Values Disappearing with Power BI Connection

Guest Writer Sep 28, 2026 870 views

Question details

The user needs to keep Excel PivotTable values visible without them disappearing when a Power BI or OLAP connection loads.

How to Fix Excel PivotTable Values Disappearing with a Power BI Connection

Product: Microsoft Excel
Device & OS: not provided
Scenario: Opening or refreshing an Excel workbook that contains a PivotTable created with 'Analyze in Excel' and connected to Power BI.
Observed behavior: PivotTable values remain visible when connections are disabled, but they disappear completely after the Power BI or OLAP connection loads in a trusted location.

Before you start

Ensure you know who your Microsoft 365 administrator is, as resolving this specific product bug requires administrative access to submit a formal support ticket.

Solution 1Recommended

Escalate to Microsoft 365 Support

Since this behavior is identified as a product limitation or bug where values disappear in trusted locations, the official resolution requires Microsoft support intervention.

Power BI PivotTables rely heavily on their active connection because the source data is stored in the cloud (Power BI) rather than locally inside the Excel workbook. When the connection loads in a trusted location and clears the visible values, it indicates a bug in how Excel processes the OLAP cache.

Contact your Administrator

Reach out to your organization's Microsoft 365 administrator and request them to open a support ticket.

Open the Admin Center

Have the administrator log into the Microsoft 365 admin center and navigate to the 'Support' section.

Submit a New Service Request

Click on 'New service request' and explicitly explain that PivotTable values remain visible when connections are disabled but disappear in a trusted location after the Power BI connection loads.

Provide Detailed Context: Make sure to mention to the support team that the issue specifically happens with workbooks created using the 'Analyze in Excel' feature.

Solution 2

Disable External Data Connections Temporarily

Use this workaround to prevent the Power BI connection from loading, allowing you to view the previously cached PivotTable values.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Analysis

If you are frustrated by connection bugs and data disappearing in Microsoft Excel, consider switching to WPS Office. It is a free, lightweight, and user-friendly alternative that provides robust spreadsheet capabilities, seamless Microsoft format compatibility, and an interface you already know.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite.
2. Install and Launch: Run the installer and open WPS Spreadsheet once the installation is complete.
3. Open Your Files: Open your existing .xlsx files directly in WPS Spreadsheet to continue analyzing your data smoothly.

Highly compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsRobust PivotTable and data analysis capabilities without complex cloud dependenciesLightweight application that runs smoothly and quickly on all devicesFree to download with a familiar, easy-to-use interface for a seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why do my PivotTable values disappear when connected to Power BI?

Power BI PivotTables depend on an active connection because the source data is stored in the Power BI service, not locally in the Excel workbook. If there is a bug or glitch when the OLAP connection refreshes in a trusted location, the local visual cache may clear, causing the values to disappear.

Can I save Power BI PivotTable data locally in Excel?

Native Power BI connections in Excel use OLAP, meaning the raw data remains in the cloud to keep the file size small. You cannot store the live raw data locally, but you can copy the aggregated PivotTable data and use 'Paste as Values' in a new sheet to keep a static, offline record.

How do I view my old PivotTable data if the connection keeps failing?

You can view the cached data by preventing the connection from loading. To do this, either open the file from a non-trusted location or go to File > Options > Trust Center > Trust Center Settings > External Content, and choose to disable all data connections.

Excel & Data Problems