How to Remove an Orphaned Table from the Excel Data Model
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.

- 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 modifying your Data Model or deleting table relationships, ensure you have saved a backup copy of your Excel workbook to prevent accidental data loss.
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.
Open your Excel workbook, navigate to the Data tab on the ribbon, and click on the 'Relationships' button located in the Data Tools group.
In the Manage Relationships dialog box, review the list to find any relationships that reference your orphaned table.
Select the specific relationship involving the orphaned table and click the 'Delete' button.
Close the dialog box and attempt to delete the orphaned table again through the Power Pivot window or Excel interface.

Delete the Table using the Power Pivot Manage Tool
If the standard Excel interface fails to delete the table, use the dedicated Power Pivot management window for direct access.
Recreate the Workbook Data Model
If the table is permanently stuck due to file corruption, transferring your active queries and data to a new workbook is the safest fallback.
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. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files directly with perfect formatting retention.
- 3. Process Data Efficiently: Utilize built-in pivot tables and data processing tools to handle your information seamlessly.

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.




