Shape / rows · All operations

Fill down or up

Fill down or up across rows, and the inverse, blank down.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
several clauses of SQL, or a window

The DuckDB step

Fill down the last known value

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id, region,
       last_value(region IGNORE NULLS) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS region_filled
FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) FILTER (WHERE region_filled IS NULL) = 0 AND max(region_filled) FILTER (WHERE order_id = 9) = 'west' FROM result)) THEN 'as expected' ELSE error('order 9 was not filled from order 8') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “order 9 was not filled from order 8”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Blank out repeated values

Erase a cell when it repeats the value above, keeping only the first of each run.

Fill blanks from the nearest value

Replace each null with the nearest non-null value above or below it, optionally within groups.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache Hop (1)
  • Repeat fieldsread 2026-09-27

    Fill a field down from the previous row, optionally within groups

DuckDB (1)
  • fillread 2026-09-27

    fill aggregate

Duckle (2)
  • Backward Fillread 2026-09-25

    replace nulls with the next non-null value in an ordered window

  • Forward Fillread 2026-09-25

    replace nulls with the most recent non-null value in an ordered window

EasyMorph (1)
  • Fill downread 2026-09-27

    Fill empty cells with the last non-empty value above

OpenRefine (2)
  • Blank downread 2026-09-27

    Erase a cell when it repeats the value above, keeping the first of each run

  • Fill downread 2026-09-27

    Copy the nearest value above into each blank cell of the column

Polars (2)
Power Query M (3)
  • Table.ClearDownread 2026-09-27

    Set named columns to null on rows where all their values repeat the row above

  • Table.FillDownread 2026-09-27

    Replace nulls in a column with the nearest non-null value above

  • Table.FillUpread 2026-09-27

    Replace nulls in a column with the nearest non-null value below

RapidMiner Studio (1)
Tableau Prep (2)
  • Fill Downread 2026-09-27

    Replace nulls with the value from the rows above, within optional groups

  • LAST_VALUEread 2026-09-27

    Last value, optionally last non-null, from the partition's first row to the current row

Trifacta (1)
  • FILL Functionread 2026-09-27

    Fill missing or null values with the most recent non-blank value within a window

VisiData (1)
  • setcol-fillread 2026-09-23

    Fill null cells of selected rows with the previous non-null value up the column

Background

In tidyr, fill() fills the missing values in chosen columns with the previous entry (filling down) or the next entry (filling up).[1]

When to use it

In OpenRefine, fill down and blank down are the two functions most frequently used on data organised into records, where several rows are associated with one entity.[2] Reach for a fill when the blanks are a matter of layout, such as a report that prints a group's name once above its rows, because there the missing value is known and only left out. (Meridian's view)

Last observation carried forward (LOCF) imputes a missing value from the value immediately before it, on the belief that an unmeasured value has not changed since it was last measured.[3] LOCF is known to increase the risk of bias and potentially false conclusions, and for that reason is not recommended.[3] When the blanks are measurements nobody took, a fill is a guess dressed as data, so think twice before filling them, and keep a column that marks which values were filled. (Meridian's view)

In tidyr, fill() on a grouped data frame works within each group and does not fill across group boundaries.[1] Fill within the key a value belongs to, so the last value of one customer or one account is never carried into the next. (Meridian's view) In tidyr, the downup direction fills first down and then up, and updown fills first up and then down.[1] In pandas, the limit argument of ffill caps how many consecutive missing values are filled, so a longer gap is only partly filled.[4] Cap the fill where a value goes stale after a while, such as a price or a status, rather than letting one reading stand for a long gap. (Meridian's view)

The method

In DuckDB, last_value with IGNORE NULLS returns the expression at the last row of the window frame among rows with a non-null value.[5] In DuckDB, first_value with IGNORE NULLS returns the expression at the first row of the window frame with a non-null value.[5] Write a fill down as last_value with IGNORE NULLS over a frame that ends at the current row, and a fill up as first_value with IGNORE NULLS over a frame that starts there. (Meridian's view)

In DuckDB, the default frame with an ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, where CURRENT ROW takes in the row's peers, unlike ROWS framing.[5] In PostgreSQL, the default window frame runs from the start of the partition through the last peer of the current row, which is likely to give unhelpful results for last_value.[6] Write the frame out with ROWS and order by a key that is unique within the partition, so rows that tie on the ordering do not take each other's values. (Meridian's view) In DuckDB, without an ordering the results of general-purpose window functions are not well-defined.[5] In DuckDB, row_number() OVER () turns the original row order into an explicit column that operations which do not preserve row order can refer to.[7] When the only order is the file's own, number the rows on load and fill by that number. (Meridian's view)

In Power Query, Table.ClearDown sets the named columns to null on a row when all their values repeat the previous row.[8]

Compared with the alternatives

In DuckDB, the fill() window function replaces NULL values by linear interpolation from the closest non-NULL values, extrapolating linearly at the ends.[5] Interpolation suits a numeric series that moves smoothly between readings, while a fill down suits a label or a state that holds until it changes. (Meridian's view) Well-known approaches to missing data besides LOCF include hot deck and cold deck imputation, mean imputation, regression imputation, stochastic imputation and multiple imputation.[3] Reach for one of those statistical methods when a missing value is a gap in a measurement, and keep a fill for values the layout left out. (Meridian's view)

Sources

  1. [1]Fill in missing values with previous or next value — fill • tidyrdocumentation
  2. [2]Cell editing | OpenRefinedocumentation
  3. [3]Imputation (statistics) - Wikipediasecondary source
  4. [4]pandas.DataFrame.ffill — pandas 3.1.1 documentationdocumentation
  5. [5]Window Functions – DuckDBdocumentation
  6. [6]PostgreSQL: Documentation: 18: 9.22. Window Functionsdocumentation
  7. [7]Order Preservation – DuckDBdocumentation
  8. [8]Table.ClearDown - PowerQuery M | Microsoft Learndocumentation