Shape · Columns

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.

Also known as: Previous row, fill down

How it works

Choose Update column to overwrite an existing column (it keeps its type) or New column with a name and a Type (Double by default). Group by starts the sequence again per group. The Formula is Add Column's dialect plus row tokens: [Row-1:col] reads col one row back, [Row+2:col] two rows ahead, and [Row-1:target], where target is the column being computed, is the value this formula produced on the previous row. The target may only be read one row back, never further or ahead. Rows are taken in the order they arrive, so sort first if the order matters. Rows past the edge read as decides whether a token beyond the first or last row gives NULL or an empty value (0 for numbers, an empty string for text).

Inputs and outputs

Inputs

data — Table

Outputs

data — Table

Settings

The Attributes panel with a Multi-row Formula node selected.

The Attributes panel of Multi-row Formula, as in the app: the controls work, but nothing runs — there is no data behind it.

The same settings, as the Assistant writes them

{
  mode?: "update"|"new"
  target: string
  outputType?: "string"|"integer"|"double"|"boolean"|"date"|"timestamp"
  expression: string
  groupBy?: string[]
  otherRows?: "null"|"empty"
}

expression = raw DuckDB SQL (Add Column's dialect) with row tokens [Row-N:col] / [Row+N:col]; [Row-1:<target>] is the value computed for the previous row (fill-down: "CASE WHEN x IS NULL THEN [Row-1:x] ELSE x END"); the target may not be read further back or ahead. mode "update" overwrites target (keeping its type), "new" appends it as outputType. Rows restart per groupBy; otherRows is what a row past the edge reads as (empty = 0 for numbers, '' for text)

Example

A spreadsheet export lists the region only on the first row of each block. Update region with CASE WHEN region IS NULL THEN [Row-1:region] ELSE region END. For a running total instead, add a new running column with [Row-1:running] + amount, group by region and let edge rows read as empty.

region	amount	running
North	10	10
NULL → North	5	15
South	7	7

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.
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.
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.