Power Query Problems

Fix a Blank Power Pivot Data View in Excel

Maira MehtabMaira Mehtab Nov 13, 2026 869 views

Question details

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

Fix a Blank Power Pivot Data View in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Inspect the model table

In Excel, select Power Pivot > Manage, open the affected table, and identify the calculated columns you added.

2
Check column dependencies

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.

3
Remove a suspect column

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.

4
Recreate the column in Power Query

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.

5
Refresh and verify

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.

Move Problematic Calculated Columns to Power Query
Check dependent model objects: Removing a column can break measures, reports, or relationships that reference it. Update those dependencies as needed; deleting all PivotTables, charts, or relationships is not necessarily required.
Free Microsoft Office alternative

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.

Open and edit compatible Excel spreadsheets in WPS SpreadsheetSupports common Microsoft Excel formats, including XLSXUse a familiar spreadsheet interface for everyday tables and formulasFree, lightweight office suite
microsoft office alternative - wps office

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.