Reshape Irregular Data into Complete Sections in Excel Power Query
Question details
Create subsection, region, and value associations from irregular data while retaining missing combinations as null.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A source contains variable-length subsections and regional values that need to be reshaped into a complete structure.
- Observed behavior
- The desired output contains every subsection-region combination, with null where no value exists.
Identify the pattern that distinguishes subsection headers from data rows and list all expected regions. Check that region names in the list match the source column names exactly.
Build a complete subsection-region grid in Power Query
Use this approach when source sections vary in length and missing combinations must remain visible.
The steps assume region names are column headers. If regions are stored differently, first reshape them into Region and Value columns.
If a subsection-region pair has multiple source values, the merge can produce multiple rows. Group or otherwise resolve duplicates using a rule appropriate for the data if one row per pair is required.
Select the source range in Excel and choose Data > From Table/Range. In Power Query Editor, use Home > Use First Row as Headers if the region names are not yet column headers.
Choose Add Column > Conditional Column and create a Subsection column that returns the header label for rows matching your header pattern and null for other rows. Select Subsection, then choose Transform > Fill > Down.
In the Queries pane, right-click the filled-down query and choose Reference. Keep only the Subsection column, filter out nulls, and choose Home > Remove Rows > Remove Duplicates. Name the query Subsections.
Return to the filled-down source query and filter out subsection header rows using the source's header pattern. Select the region columns and choose Transform > Unpivot Columns. Rename Attribute to Region, and keep Subsection, Region, and Value.
In Excel, create a one-column table named Regions with a Region header and every expected region, including regions with no values. Select it and choose Data > From Table/Range; name the resulting query Regions.
Open the Subsections query and choose Add Column > Custom Column. Enter Regions as the formula, then expand the new column using its expand button and select the Region field.
Choose Home > Merge Queries. Select Subsection and Region in that order in the grid and in the normalized source query, choose Left Outer as the join kind, and select OK. Expand the merged column to include Value, then choose Home > Close & Load.

Review Reshaped Data with WPS Spreadsheet
WPS Office is a free, lightweight Office alternative with a familiar interface. Open an exported Excel workbook to review and edit the reshaped data; WPS does not fix or replace the Excel Power Query workflow.

Frequently Asked Questions
Why should I fill subsection labels down before removing header rows?
The header rows supply the labels for the data rows below them. Use Transform > Fill > Down on the Subsection column before filtering out the headers.
How do I include a region that has no values anywhere in the source?
Add it to the Regions table in Excel before importing or refreshing that table. The cross-join will create a row for that region under every subsection.
Why are some subsection-region pairs repeated after the merge?
The normalized source may contain more than one matching value for a pair. Use Home > Group By or another appropriate rule to resolve duplicates if you need one row per pair.
What happens when I refresh the queries?
Use Data > Refresh All in Excel to rerun the queries against the updated source and region table. New subsections and listed regions are included in the rebuilt grid.




