Shape · Columns

Fill Missing

Replaces blanks with a fixed value, an empty string, or the previous or next value, or drops those rows. It can also drop rows or columns that are entirely empty.

Also known as: Fill nulls, forward fill, data cleansing

How it works

Click Add rule, pick one or more columns (or every text or numeric column of the input) and a strategy. Constant writes the value you type and needs one; Blank text writes an empty string; Drop rows removes rows where that column is null; Forward fill carries the last non-null value down and Backward fill the next one up, both in the order rows arrive. When two rules cover the same column the later one wins. Two node-wide options sit below the rules: Drop rows where every column is null and Drop columns where every value is null. Only nulls are touched; an empty string is a value and stays as it is.

Inputs and outputs

Inputs

data — Table

Outputs

data — Table

Settings

The Attributes panel with a Fill Missing node selected.

The Attributes panel of Fill Missing, as in the app: the controls work, but nothing runs — there is no data behind it.

The same settings, as the Assistant writes them

{
  fills: [{ column: string, columns?: string[], allTextColumns?: boolean, allNumericColumns?: boolean, strategy: "constant"|"blank"|"drop_rows"|"forward_fill"|"backward_fill", value?: string }]
  dropNullRows?: boolean
  dropNullColumns?: boolean
}

one rule can cover several columns: list them in columns, or set allTextColumns / allNumericColumns to apply it to every text / numeric column of the input. constant needs a non-empty value; blank fills text columns with an empty string. dropNullRows drops rows where every column is NULL, dropNullColumns drops columns that are NULL in every row

Example

A merged sheet repeats the customer only on the first line of each order and leaves discount empty when there is none. Add a Forward fill rule on customer and a Constant rule on discount with the value 0.

customer	discount	customer (after)	discount (after)
Acme	NULL	Acme	0
NULL	5	Acme	5
Bolt	NULL	Bolt	0

Select Columns — Keeps only the columns you pick and drops the rest.
Rename — Gives columns new names. Type old-to-new pairs, or let the data decide at run time: take the names from the first row (which is then dropped), from a names table on a second input, or from an expression over each old name.
Cast Types — Changes a column's type, such as text to number or text to date. You choose what happens to a value that won't convert: stop the run, or make it blank with or without a warning.
Parse — Turns messy text into real values: dates and times, numbers with separators or currency signs, true/false, durations, measurements with units, and coordinates.
Add Column — Adds a column worked out by a formula, a fixed value, today's date or the current time, or a parameter. For example, margin = Price − Cost. Excel's finance functions such as PMT work here by name; see Formulas and finance functions.
Multi-field Formula — Applies one formula to many columns at once, such as rounding every price column. Overwrite the columns or write new ones beside them.
Multi-row Formula — Works out a column from the current row and its neighbours, including the column's own value on the previous row. Use it for fill-down, running totals or carrying the last value forward.
Replace Values — Swaps text in a column for other text, such as 'N/A' to blank or 'NY' to 'New York'. Each rule replaces its text wherever it appears in a value, as plain text or a pattern.
Replace via Mapping — Swaps values in one or more columns using a second table of from → to pairs, such as a code list turning 'NY' into 'New York'. Values with no match stay as they are, or fail the run if you choose.
Interpolate NULLs — Fills blank numbers by estimating from the known values around them, in a straight line or by taking the nearest one. For example, missing sensor readings.
Clip Values — Caps numbers at a lower and/or upper bound. Anything below becomes the minimum, anything above becomes the maximum.
Bin — Turns a number into a labelled band, using your own break points or equal-width buckets. For example, ages into 0–17, 18–64 and 65+.
Format Table — Sets how columns print in previews and reports: currency, percent, decimals, thousands separators, date masks, alignment and hidden columns. The values themselves don't change.
Format to Text — Turns a number, date or time column into formatted text: thousands separators, fixed decimals, currency or a date pattern. The pattern can come from another column, row by row. Unlike Format Table, this changes the data.
Audit Stamp — Adds columns that record when the row was loaded, from which source, and in which run. Use it before writing to a warehouse so you can trace every row.