Power Query Problems

How to Fill Down Surnames in Excel Power Query

Camila Milosovich Sep 27, 2026 869 views

Question details

The user needs to extract surname header rows and fill them down into the corresponding individual burial records using Power Query.

How to Fill Down Surnames in Excel Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Organizing hierarchical data where surnames appear as header rows above individual records, such as burial dates and first names.
Observed behavior: Surnames need to be populated into a new column for each related record, and the original header rows must be removed to create a clean, flat dataset.

Before you start

Ensure your burial records dataset is imported into Power Query and identify the logic that distinguishes a surname header row from a standard record (e.g., a null value in the Date column).

Solution 1Recommended

Use Conditional Column and Fill Down in Power Query

Create a conditional column to capture the surname, fill it down to all related rows, and filter out the original header rows.

By identifying a pattern in your data, such as empty date fields on rows containing only a surname, you can conditionally extract the surname and apply Power Query's Fill Down feature to cascade the data to related records.

Add a Conditional Column

Navigate to the 'Add Column' tab and select 'Conditional Column'. Name the new column 'Surname'. Set the logic: If the 'Date' column equals 'null', output the value from the 'Name' column; otherwise, output 'null'.

Fill Down the Surnames

Right-click the header of your new 'Surname' column, hover over 'Fill', and select 'Down'. This action will duplicate the surname into all subsequent rows until the next surname is encountered.

Remove Original Header Rows

Click the filter drop-down arrow on the 'Date' column and uncheck the 'null' (or blank) option. This filters out the original header rows that only contained the surnames.

Format and Load Data

Reorder your columns by dragging the 'Surname' column to your preferred position. Apply the correct data types to your columns, then go to the 'Home' tab and click 'Close & Load' to return the cleaned dataset to your worksheet.

Use Conditional Column and Fill Down in Power Query

Reusable Transformation: Once configured, these Power Query steps are saved. If you import additional pages or update the source burial records, simply click 'Refresh' to automatically clean the new data.

Free Microsoft Office alternative

Need a Lightweight Tool for Data Management? Try WPS Office

While Power Query is a powerful feature in Microsoft Excel, it can be resource-heavy or overly complex for simpler tasks. WPS Office offers a free, lightweight, and user-friendly spreadsheet application with seamless compatibility for Microsoft formats. You can easily organize, filter, and clean your datasets using intuitive tools and formulas without a steep learning curve.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Free, lightweight, and uses minimal system resources.Clean, familiar user interface for immediate productivity.Robust formula and filtering tools for quick data cleaning tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why did Fill Down not work on my newly created column?

Fill Down only works on 'null' values in Power Query. If your conditional column resulted in empty text strings ("") instead of true nulls, Fill Down will ignore them. Ensure your conditional column logic outputs the actual 'null' keyword when conditions are not met.

Can I use this method if the surname header spans multiple columns?

Yes, but you may need to merge the header columns first or adjust your conditional column logic to check for nulls across multiple adjacent columns before extracting the surname.

How do I remove the blank rows after filling down the surnames?

You can filter out the original header rows by clicking the filter drop-down on a column that should always have data for a valid record (such as the Date or Record ID column) and unchecking the 'null' or '(Blank)' option.

Excel & Data Problems