Unstack Uneven Excel Data into Rows with Power Query
Question details
Convert uneven data spread across columns or a pivot-style layout into a normalized row-by-row table.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Repeated values are arranged in columns, possibly with different numbers of entries or separate data blocks.
- Observed behavior
- The desired result is a table with one row per value, its associated identifier, and any relevant column label.
Identify the columns that contain identifiers and the columns whose values should become rows. Make sure each source column has a clear header and that Power Query is available in your Excel version.
Unpivot the uneven columns in Excel Power Query
Use this method when column headers represent repeated categories, periods, or other attributes that should become row values.
Select a cell in the source range and choose Data > From Table/Range. Confirm the range and select My table has headers if applicable.
In Power Query Editor, select the columns that identify each source row, such as Customer or Product. Hold Ctrl to select more than one identifier column.
With the identifier columns selected, choose Transform > Unpivot Columns > Unpivot Other Columns. Power Query creates Attribute and Value columns while retaining the identifiers.
Open the filter on the Value column and exclude null or blank entries if they appear. Unpivoting normally omits null values, but empty text may still need filtering.
Double-click Attribute and Value to give them meaningful names, then choose Home > Close & Load to create the normalized table in Excel.

Work with Excel tables in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files to review and edit normalized tables; WPS does not fix Excel Power Query issues or replace Power Query transformations.

Frequently Asked Questions
Which columns should I select before unpivoting?
Select the identifier columns, such as Product or Customer, then choose Transform > Unpivot Columns > Unpivot Other Columns. The remaining columns become attribute-value rows.
How do I remove blank entries from the unpivoted table?
In Power Query Editor, open the filter on the Value column and exclude null or blank entries. Null values are normally omitted during unpivoting, but empty text can remain.
Can I combine several unpivoted tables?
Yes. If the tables have matching column structures, choose Home > Append Queries in Power Query Editor. To match related data side by side, use Merge Queries with a shared key.
How do I update the result after changing the source data?
After editing the source table in Excel, use Data > Refresh All to rerun the Power Query transformation and update the loaded result.




