Use Excel Power Query to Add the Previous Header to Each Data Row
Question details
Turn grouped source rows into a table with Name, Header, and Category columns.

- 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.
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.
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.
Select a cell in the source range and choose Data > From Table/Range. Confirm the table range and headers to open Power Query Editor.
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.
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.
Select the new Header column, then choose Transform > Fill > Down. Each null cell receives the header from the preceding header row.
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.
Choose Home > Close & Load to return the transformed table to Excel. After changing the source table, right-click the loaded result and select Refresh.

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.

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.




