Excel Power Query: Fill Down Group Labels for Detail Rows
Question details
The user needs to assign each section heading to every detail row in its group, even when groups contain different numbers of rows and blank separators.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Preparing grouped source data for a pivot table or another structured analysis workflow.
- Observed behavior
- Section headings appear only once, detail rows below them have blank group values, and blank rows separate sections.
Identify which column or combination of columns distinguishes a section heading from a detail row. Make sure the source range includes a header row, then convert it to an Excel table with Insert > Table before loading it into Power Query.
Fill group labels down in Excel Power Query
Use this method when section headings and detail rows are mixed in one table and the group lengths vary.
Power Query can create a temporary group-label column that keeps the heading only on heading rows, then propagates the last heading through the detail rows. Removing blank separator rows afterward produces a clean dataset for pivot tables.
The exact heading test depends on the source layout. For example, a heading may have a value in the first column while a detail row has values in several other columns.
Select a cell in the source range, open the Data tab, choose From Table/Range, and confirm that the table has headers to open Power Query Editor.
In Power Query Editor, select Add Column > Custom Column. Enter a conditional expression that returns the heading value for heading rows and null for detail rows, such as if [DetailColumn] = null then [SectionColumn] else null. Replace the column names and condition with those used by your table.
Select the new group-label column, open Transform > Fill > Down, and let Power Query copy the most recent nonblank heading into the detail rows below it.
Use the filter on a column that should contain data, or choose Home > Remove Rows > Remove Blank Rows, to remove blank separator records.
Remove temporary columns if needed, use Transform > Unpivot Columns when the source requires reshaping, then select Home > Close & Load to return the cleaned table to Excel.
Use WPS Office to Review and Continue Working with Cleaned Excel Data
Power Query is a Microsoft Excel feature, so WPS Office does not directly run Power Query queries. After the data is transformed in Excel, WPS Office provides a free, lightweight spreadsheet environment for opening, reviewing, filtering, analyzing, and sharing the resulting workbook with a familiar interface and strong Microsoft Office format compatibility.

Frequently Asked Questions
How do I decide which rows are section headings in Power Query?
Inspect the columns that differ between headings and detail rows. In Add Column > Custom Column, create a condition based on that difference, such as a blank detail field or a populated section-name field, and return null for detail rows.
Why does Fill Down leave some rows blank?
Fill Down only works after a non-null value has appeared above the row. Check that the custom heading column returns the correct label, remove unexpected blank rows, and sort the data into the intended section order before using Transform > Fill > Down.
Can Power Query handle groups with different numbers of detail rows?
Yes. Fill Down continues each heading until the next non-null heading, so each section can contain a different number of detail rows. Blank separator rows should be removed after the fill operation.
How can I prepare the result for a pivot table?
After filling the labels, filter out blank records, remove temporary columns, and use Transform > Unpivot Columns if several measure columns need to become attribute-value pairs. Select Home > Close & Load to create the clean table in Excel.




