Power Query Problems

Use Excel Power Query to Add the Previous Header to Each Data Row

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

Turn grouped source rows into a table with Name, Header, and Category columns.

Add the Previous Header to Each Data Row with Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Each group starts with a header and includes a summing row, while the desired output contains one row per name and category.
Observed behavior
Each data row should contain its preceding group header, without retaining the header or summing rows as data records.
Before you start

Identify how header rows and summing rows can be distinguished from data rows, and check where the name and category values are stored. Keep the source rows in their original group order so each header can be filled down to the correct records.

Solution 1Recommended

Fill group headers down and filter out non-data rows

Use this approach when each name row already has its corresponding category and header rows can be identified consistently.

The exact column names and header-row condition depend on the source table. The custom-column example below assumes a RowType column identifies headers and that the header text is in the Name column; replace these references with those in your data.

1
Import the source table

Select a cell in the source range and choose Data > From Table/Range. Confirm the table range and headers to open Power Query Editor.

2
Preserve the source order

If you need a way to restore the original order, choose Add Column > Index Column > From 0. Avoid sorting the rows before filling down the headers.

3
Create a header column

Choose Add Column > Custom Column and name it Header. For the example layout, enter if [RowType] = "Header" then [Name] else null; adjust the condition and column references to identify the actual header rows.

4
Fill headers down

Select the new Header column, then choose Transform > Fill > Down. Each null cell receives the header from the preceding header row.

5
Keep only data records

Use the column filter menus to exclude header rows and summing rows, using the markers present in your source. Keep or rename the columns so the result contains Name, Header, and Category.

6
Load and refresh the result

Choose Home > Close & Load to return the transformed table to Excel. After changing the source table, right-click the loaded result and select Refresh.

Fill group headers down and filter out non-data rows
Check how categories align: If categories are stored in separate rows rather than on the corresponding name rows, filling down headers alone will not pair them. You will also need to match names and categories within each group, for example by assigning positions and merging the resulting queries.
Free Microsoft Office alternative

Work with grouped spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Excel files. It makes it easy to move routine spreadsheet work to WPS, although it does not fix Excel Power Query or guarantee that its queries will refresh.

Review and edit grouped spreadsheet data in WPS SpreadsheetOpen and save Microsoft Excel formats such as XLSXUse a familiar interface for everyday spreadsheet tasksMove existing Office files into a lightweight, free suite
microsoft office alternative - wps office

Frequently Asked Questions

Why should I fill down the header before removing header rows?

The header rows supply the values that Transform > Fill > Down copies into the rows below. If you remove them first, those values are no longer available to fill.

What if my table does not have a RowType column?

In Add Column > Custom Column, replace the example condition with one based on a reliable marker in your source, such as a value or pattern that identifies header rows. The condition must distinguish headers from ordinary data rows.

How do I update the output after adding source rows?

Add the rows to the Excel source table, then right-click the loaded query result and select Refresh. Make sure the new rows follow the same structure as the existing groups.

Will filling down headers also match categories to names?

It will not create a name-to-category match by itself. If categories are on separate rows, create a position within each group for both lists and merge them on the group and position before retaining the final columns.