Power Query Problems

How to Restore Removed Columns in Power Query Unpivot

Huda Qurayshi Oct 1, 2026 868 views

Question details

Users need to retrieve specific data columns that disappeared from the output table after performing an unpivot data operation.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Unpivoting a dataset where specific identifier columns fail to appear in the final output table.
Observed behavior: Columns such as 'Assigned To' and 'Task Name' are missing from the unpivoted Power Query result due to being excluded, containing no usable data, or incorrect identifier selection.

Before you start

Before modifying your query steps, ensure that the missing columns actually exist in your original source data and contain the necessary values without being entirely blank.

Solution 1Recommended

Review and Edit Power Query Applied Steps

Use this solution to identify which step removed your columns and adjust your unpivot settings to keep them as identifier columns.

Power Query records every transformation as an 'Applied Step'. When columns go missing during an unpivot operation, it usually means they were not selected as identifier columns or an earlier step explicitly removed them.

Open Power Query Editor

In Excel, go to the 'Data' tab and click on 'Get Data' > 'Launch Power Query Editor', or double-click your existing query in the Queries & Connections pane.

Inspect the Applied Steps

Look at the 'Applied Steps' list on the right side of the screen. Click on the step just before the 'Unpivoted Columns' step to verify that your 'Assigned To' and 'Task Name' columns are still present.

Adjust Identifier Columns

Delete the current 'Unpivoted Columns' step by clicking the 'X' next to it. Select the columns you want to keep intact (e.g., 'Assigned To', 'Task Name'), right-click the column header, and choose 'Unpivot Other Columns'.

Refresh and Load

Confirm the missing columns are now visible alongside your unpivoted attributes and values. Click 'Close & Load' in the top-left corner to apply the updated result back to your worksheet.

Review and Edit Power Query Applied Steps

Best Practice: Always use 'Unpivot Other Columns' instead of 'Unpivot Columns'. This ensures that any new columns added to your source data in the future will automatically be unpivoted, while your specified identifier columns remain safe.

Free Microsoft Office alternative

Experience Seamless Data Management with WPS Office

If complex features like Power Query are slowing down your workflow, consider switching to WPS Office. It provides a lightweight, intuitive, and highly compatible alternative for your spreadsheet needs, allowing you to manage and analyze data effortlessly.

1. Download and Install: Get the free WPS Office suite from the official website and install it on your device.
2. Open Your Data File: Launch WPS Spreadsheet and easily open your existing .xlsx workbooks.
3. Analyze Effortlessly: Use built-in PivotTables and data management tools to reshape and analyze your data without complex query editors.

Free to use with comprehensive spreadsheet and data analysis capabilitiesHigh format compatibility with Microsoft Excel (.xlsx) filesLightweight application that runs smoothly on most devices without heavy add-insFamiliar user interface for a seamless, zero-learning-curve transition

microsoft office alternative - wps office

Frequently Asked Questions

Why do columns disappear when I unpivot in Power Query?

Columns disappear if they are included in the unpivot selection itself, turning their headers into 'Attributes' and data into 'Values'. They can also disappear if an earlier 'Remove Columns' step deleted them, or if they contained null values and were filtered out.

How do I choose which columns to keep during an unpivot?

To keep specific columns as static identifiers (like names or IDs), highlight only those columns in the Power Query Editor, right-click the header, and select 'Unpivot Other Columns'.

Can I undo a step in Power Query Editor?

Yes. Power Query doesn't use a traditional 'Undo' button. Instead, you can delete or modify any action by clicking the 'X' or the gear icon next to the step in the 'Applied Steps' pane on the right.

Excel & Data Problems