Power Query Problems

Reshape Irregular Data into Complete Sections in Excel Power Query

Elise WilliamsElise Williams Nov 18, 2026 868 views

Question details

Create subsection, region, and value associations from irregular data while retaining missing combinations as null.

Reshape Irregular Data into Complete Sections in Excel Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Import the source

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.

2
Identify and fill subsection labels

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.

3
Create the subsection list

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.

4
Normalize the source values

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.

5
Create the region list

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.

6
Cross-join subsections and 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.

7
Merge the values

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.

Build a complete subsection-region grid in Power Query
Why missing values remain visible: The subsection-region grid supplies every expected pair before the left join. Pairs without a matching source value have null in the expanded Value column.
Free Microsoft Office alternative

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.

Review subsection, region, and value tables in a familiar spreadsheet interfaceOpen and edit Microsoft Excel XLSX files for easier migrationUse a free, lightweight office suiteWork with familiar spreadsheet controls
microsoft office alternative - wps office

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.