Filling Missing Category Labels Using Forward Fill

Someone sends you an Excel export of a sales report. You open it and the category column looks like this: "Electronics" on the first row, then fifteen blank rows, then "Clothing" on row seventeen, then twelve more blanks. The original report looked fine in Excel because the merged cells made it obvious which items belonged to which category. Flattened into rows, only the first cell of each merge survives.

This pattern shows up constantly in data exported from Excel, PDF-to-CSV conversions, and reporting tools that display group headers only once. The category information is technically there - it's just missing from most rows. You need every row to carry its own category label.

One thing to settle before you start, because it decides whether the fix below works at all: a blank cell can arrive as a NULL or as an empty string, and they are not the same value. Fill Missing only touches NULLs. The next section covers how to tell which one you have and what to do if it's the empty string.

What forward fill does

Forward fill takes the last non-empty value and copies it down into the empty rows below. When it hits another non-empty value, it starts using that one instead. It walks through the data top to bottom, carrying each value forward until it finds a replacement.

Before:

• Electronics
• NULL
• NULL
• Clothing
• NULL
• NULL
• NULL

After:

• Electronics
• Electronics
• Electronics
• Clothing
• Clothing
• Clothing
• Clothing

First: NULL or empty string?

Fill Missing generates a COALESCE, and COALESCE only replaces NULL. A cell holding '' is a real value as far as SQL is concerned, so the fill walks straight past it. On screen the two look identical, which is why this trips people up.

Which one you get depends on the route the file took. An unquoted empty field in a CSV usually lands as NULL. A quoted "", or a blank that came through a tool that writes explicit empties, lands as an empty string.

The fastest test is to just run the fill and look. If the blanks are still blank afterwards, they were empty strings. If you'd rather know up front, add a Filter step, switch the condition builder to the SQL Expression tab and enter category = ''. Rows come back, you have empty strings. Nothing comes back, they're NULL. Delete the filter step afterwards.

To convert empty strings to NULL so the fill can see them, add Update Values from the Transform group before it. Set Apply results into to the category column, open the condition builder and pick category is Empty, then leave the Value box blank. A blank value emits NULL, so the step generates:

CASE WHEN (category IS NULL OR category = '') THEN NULL ELSE category END

The "is Empty" operator on a text column is defined as NULL or empty string, so this is safe to run whichever kind of blank you have. If you prefer one line, a SQL Query step doing SELECT * REPLACE (NULLIF(category, '') AS category) FROM pipeline_output does the same thing.

Applying forward fill in ExploreMyData

Open Fill Missing from the Data group. Select the category column and choose "forward" as the method. Leave Sort by empty to fill in the current row order, or pick a column if the fill should follow that instead.

One click. That's the whole operation.

Behind the scenes, ExploreMyData generates a window function:

COALESCE(category, LAST_VALUE(category IGNORE NULLS) OVER (ORDER BY __rn__ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))

Here's what this does: for each row, if the category is not NULL, use it. If it is NULL, look back across every row up to this one and grab the most recent non-NULL value. LAST_VALUE with IGNORE NULLS over the whole preceding window is what makes a run of three or ten consecutive blanks all fill, rather than just the first blank after a value. The generated __rn__ row number preserves the original row order, which is critical - forward fill only makes sense when rows are in the right sequence.

Handling NULLs at the top

Forward fill has one blind spot: rows before the first non-empty value. If the very first row of the category column is NULL (maybe the report had a header section), forward fill can't look backward because there's nothing there yet. Those rows stay NULL.

Fix this with Update Values and a condition: category is Empty, value "Uncategorized" or whatever default makes sense for your data. Note the operator name. A text column has no "is NULL" option, because "is Empty" already covers both NULL and the empty string. That catches any stragglers forward fill couldn't reach.

You could also do this first, before the forward fill, if you know the top rows are header junk. Or skip it entirely if your data always starts with a real category.

When row order matters

Forward fill depends entirely on the order of rows. If you sort the data before applying forward fill, you'll get wrong results. The fill is based on the physical position of rows, not any logical grouping.

If your data has already been sorted or shuffled, forward fill won't work correctly. You need the rows in their original order - the order they had in the source report where the category labels were placed at group headers.

In ExploreMyData, pipeline steps run in sequence. If an earlier step reordered the rows, consider whether that is disrupting the natural grouping. You may need to fill first, then reorder. If a specific column defines the right sequence, put it in the Sort by field on the Fill Missing panel and the fill will follow that order instead of the physical one.

Multiple columns with the same problem

Some reports have this pattern on more than one column. Maybe both "category" and "department" are sparse. Apply forward fill to each column separately. Each one is its own pipeline step. The order doesn't matter here - they're independent fills on independent columns.

After filling: group and analyze

Once every row carries its category label, your data is ready for real analysis. Group by category to get totals. Pivot to compare categories side by side. Filter to a single category. None of it worked while two thirds of the category column was blank.

One catch on the aggregation itself: price in this export is text, because of the dollar signs. AVG has no meaning on text and DuckDB will refuse the query rather than guess. Add a Convert Type step on price to numeric first. It strips the $ for you, so it's one step, not three.

Then Group & Aggregate on category, with COUNT for the row count, SUM on units_sold and AVG on the converted price:

The whole thing is three steps: convert empty strings to NULL if you have them, forward fill the category, convert price to numeric. Everything after that is analysis. Reload a fresh export from the same report and the pipeline replays against it.

Fill in your missing categories →