Power Query Problems

Excel Power Query: Fill Down Group Labels for Detail Rows

John WilsonJohn Wilson Nov 12, 2026 870 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Load the source table

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.

2
Create a heading marker

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.

3
Fill the group value downward

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.

4
Remove separator rows

Use the filter on a column that should contain data, or choose Home > Remove Rows > Remove Blank Rows, to remove blank separator records.

5
Reshape and load the result

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.

Works with large datasets: Power Query is well suited to processing tens of thousands of rows without maintaining complex worksheet formulas for every record.
Free Microsoft Office alternative

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.

Open and review cleaned group-label data in a lightweight spreadsheet application.Use familiar sorting, filtering, tables, and pivot-table workflows for follow-up analysis.Maintain compatibility with Microsoft Excel .xlsx and legacy .xls workbooks.Move between Microsoft Office and WPS Office with minimal layout and formatting changes.
microsoft office alternative - wps office

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.