Power Query Problems

Unstack Uneven Excel Data into Rows with Power Query

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 869 views

Question details

Convert uneven data spread across columns or a pivot-style layout into a normalized row-by-row table.

Unstack Uneven Excel Data into Rows with Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Load the source table

Select a cell in the source range and choose Data > From Table/Range. Confirm the range and select My table has headers if applicable.

2
Keep identifier columns

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.

3
Unpivot the value columns

With the identifier columns selected, choose Transform > Unpivot Columns > Unpivot Other Columns. Power Query creates Attribute and Value columns while retaining the identifiers.

4
Remove empty values

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.

5
Rename and load the result

Double-click Attribute and Value to give them meaningful names, then choose Home > Close & Load to create the normalized table in Excel.

Unpivot the uneven columns in Excel Power Query
Combining separate blocks: If separate blocks have the same normalized columns, unpivot each block as a query and use Home > Append Queries. If columns must be matched side by side, use Merge Queries with a reliable shared key instead.
Free Microsoft Office alternative

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.

Review and edit row-by-row spreadsheet tablesOpen and save compatible Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for an easier transitionGet a free, lightweight office suite
QA img-9

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.