Power Query Problems

How to Remove an Orphaned Table from the Excel Data Model

Nimra MalikNimra Malik Nov 2, 2026 869 views

Question details

The user needs to remove a table from the Excel Data Model that was left behind after its associated Power Query was deleted.

How to Remove an Orphaned Table from the Excel Data Model
Product
Microsoft Excel
Device & OS
not provided
Scenario
Deleting a Power Query connection without properly removing its associated table, causing the table to be stuck in the Data Model.
Observed behavior
Excel warns that the table must be removed through Power Query, preventing standard deletion and leaving the table orphaned in the Data Model.
Before you start

Before modifying your Data Model or deleting table relationships, ensure you have saved a backup copy of your Excel workbook to prevent accidental data loss.

Solution 1Recommended

Remove Table Relationships in the Data Model

Removing any active relationships tied to the orphaned table often allows Excel to safely delete the table from the Data Model.

When a query is deleted but the table remains, existing relationships can lock the table in place. Clearing these references manually is the primary method to resolve the error.

1
Open Data Relationships

Open your Excel workbook, navigate to the Data tab on the ribbon, and click on the 'Relationships' button located in the Data Tools group.

2
Locate Orphaned References

In the Manage Relationships dialog box, review the list to find any relationships that reference your orphaned table.

3
Delete Relationships

Select the specific relationship involving the orphaned table and click the 'Delete' button.

4
Remove the Table

Close the dialog box and attempt to delete the orphaned table again through the Power Pivot window or Excel interface.

Remove Table Relationships in the Data Model
Verification: Once the relationships are cleared, the warning prompt should disappear, allowing standard table management.
Free Microsoft Office alternative

Experience Lightweight and Fast Spreadsheets with WPS Office

While advanced Data Model corruption can be frustrating in Microsoft Excel, WPS Office provides a lightweight, highly compatible, and seamless spreadsheet experience for your everyday data processing needs without the heavy overhead.

  1. 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
  2. 2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files directly with perfect formatting retention.
  3. 3. Process Data Efficiently: Utilize built-in pivot tables and data processing tools to handle your information seamlessly.
Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Lightweight architecture ensures fast loading even for large datasets.Familiar user interface that requires zero learning curve for Excel users.Free built-in tools for data analysis, pivot tables, and charting without complex dependencies.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say a table must be removed through Power Query?

This occurs when a table was loaded directly into the Data Model via Power Query. If the query is deleted from the Queries & Connections pane without removing the data load destination first, the table remains locked by a ghost connection.

Can I clear the entire Data Model and start over?

Yes, but this is a destructive action. You can clear the Data Model by disabling the Power Pivot COM add-in and then re-enabling it, or by removing all related data connections, which will erase all customized relationships and data structures within the model.

How do I view all relationships in my Excel workbook?

Go to the Data tab on the Excel ribbon and click 'Relationships' to open a dialog box showing all active table links. Alternatively, you can open the Power Pivot window and switch to 'Diagram View' for a visual representation.