Fix a Blank Power Pivot Data View in Excel
Question details
The user needs to restore visible records in Power Pivot Data View while preserving a working data model and reports.

- Product
- Microsoft Excel Power Pivot and Power Query
- Device & OS
- not provided
- Scenario
- A Power Query table is loaded into the Excel data model and used in reports and measures.
- Observed behavior
- Power Pivot Data View becomes blank after briefly showing records during refresh, although reports, measures, and refreshed values continue to work.
Identify the affected table and any calculated columns added directly in Power Pivot. Check whether reports, measures, or relationships depend on those columns before changing them.
Move Problematic Calculated Columns to Power Query
Use this approach if the blank view began after adding calculated columns to a Power Query table in Power Pivot.
In the reported case, removing calculated columns added in Power Pivot restored the Data View. Power Query uses M rather than DAX, so a calculated column formula may need to be rewritten.
In Excel, select Power Pivot > Manage, open the affected table, and identify the calculated columns you added.
Review measures, PivotTables, charts, and relationships that use each column before removing it. Note the column names and formulas so you can recreate the logic.
In the Power Pivot window, right-click a suspect calculated column header and choose Delete Column. Repeat as needed, then check whether the table records reappear.
In Excel, open Data > Queries & Connections, right-click the source query, and select Edit. Choose Add Column > Custom Column, enter the equivalent Power Query formula, and select OK.
Select Home > Close & Load in Power Query Editor, then use Data > Refresh All in Excel. Reopen Power Pivot > Manage and check the Data View and affected reports.

Check for Conflicts with Other Excel COM Add-ins
Use this check if calculated columns do not explain the blank view or the issue persists after moving them.
Work with Excel Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for everyday spreadsheet work, with a familiar interface and Excel file compatibility to help you move between office suites. It does not repair Excel Power Pivot or guarantee support for data model features.

Frequently Asked Questions
Why do my reports work when Power Pivot Data View is blank?
The blank view can be a display or model-related issue even when the underlying data still supports reports and measures. Check calculated columns in Power Pivot > Manage, especially those added to tables loaded from Power Query.
How do I add a column in Power Query instead of Power Pivot?
Open Data > Queries & Connections, right-click the query, and select Edit. In Power Query Editor, choose Add Column > Custom Column, enter the formula, and select Home > Close & Load.
Can I use the same DAX formula in Power Query?
Not directly. Power Pivot calculated columns use DAX, while Power Query custom columns use M. Recreate the intended calculation using the appropriate Power Query expression and check the results after refresh.
What if Power Pivot does not load in Excel safe mode?
Restart Excel normally and inspect other add-ins under File > Options > Add-ins > COM Add-ins > Go. Temporarily disable other add-ins while keeping Power Pivot enabled, then reopen Power Pivot > Manage to test.




