Remove Columns Safely from a Self-Referencing Excel Power Query Table
Question details
Remove unneeded columns from a self-referencing Power Query workflow without breaking later query steps or rebuilding the table.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user wants to simplify a query table that references itself or is used by dependent queries.
- Observed behavior
- Removing a column can cause later Applied Steps or dependent queries to fail when they still reference that column.
In Power Query Editor, open the query and review the Applied Steps pane from top to bottom. Identify whether the columns are used in later transformations, merges, custom columns, grouping, sorting, or dependent queries before removing them.
Update dependent Power Query steps before removing columns
Use this method when the columns are referenced by later steps in the same query or by related self-referencing queries.
Power Query evaluates transformations in sequence. A column can be removed only after every later step that uses it has been deleted, edited, or moved earlier in the query.
In Excel, select a table connected to the query, then go to Data > Queries & Connections. Right-click the query and select Edit.
In the Query Settings pane, select each Applied Step after the point where you plan to remove the column. Check the formula bar or select Home > Advanced Editor to find references to the column name.
Update steps such as Changed Type, Renamed Columns, Removed Other Columns, Added Custom, Grouped Rows, Merged Queries, and Sorted Rows so they no longer use the column. Delete a step only if its result is no longer needed.
Select the columns in the Power Query preview, then choose Home > Remove Columns. Alternatively, choose Home > Choose Columns to keep only the fields needed by the final output.
Select Home > Close & Load, then use Data > Refresh All in Excel. Confirm that the query refreshes without an Expression.Error and that dependent queries still return the expected fields.
Use a separate final-output query to hide columns without changing the base query
Use this approach when the self-referencing base query is reused by other queries and you want to avoid changing its schema.
Use WPS Office for Lightweight Spreadsheet Table Management
Microsoft Power Query dependencies must be repaired in Excel itself. For everyday spreadsheet editing, table cleanup, filtering, formulas, and report preparation, WPS Office is a free and lightweight Office alternative with a familiar interface, strong format compatibility, and an easy transition from Microsoft Office files.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheet from the home screen.
- 2. Open your Excel workbook: Select Open and choose the existing .xlsx or .xls workbook that contains the table or report.
- 3. Manage visible table columns: Select the worksheet table, then use Data > Filter or right-click a column header to organize, hide, or adjust columns for reporting.
Frequently Asked Questions
Why does Power Query show an error after I remove a column?
A later Applied Step is still using that column. In Power Query Editor, inspect the steps after the removal point, especially Changed Type, Renamed Columns, Added Custom, Grouped Rows, and Merge operations.
Should I use Remove Columns or Choose Columns in Power Query?
Use Home > Remove Columns when you know which fields should be deleted. Use Home > Choose Columns when it is safer to explicitly retain only the columns required in the final output.
Can I remove a column from a base query without affecting referenced queries?
Not if referenced queries need that field. Create a new query with right-click > Reference, then remove or choose columns in the new output query while leaving the base query unchanged.
How can I find where a Power Query column is being used?
Select each item in the Query Settings > Applied Steps list and inspect the formula bar. For a complete view, select Home > Advanced Editor and search for the column name in the M code.
Does deleting a column in Power Query delete it from the original Excel source table?
No. Removing a column in Power Query changes the query output only. However, refreshes, worksheets, data models, or dependent queries that expect the removed output field may be affected.
