Shape · Columns

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.

Also known as: Formula, calculated field

How it works

Click Add column, give the column a name and choose what fills it. Formula is a DuckDB SQL expression over the input's columns; double-quote a name with spaces, and write conditions as IF qty > 10 THEN 'bulk' ELSE 'single' or a CASE whose END you may leave out. Value writes one fixed value cast to Text, Integer, Double, Boolean or Date. Today and Now write the current date and timestamp. Param reads the first row of the chosen column of a table wired into the params port, so one small table can drive several nodes. Excel's finance functions such as PMT are available by name in a formula; the presets in the add-node menu fill one in for you.

Inputs and outputs

Inputs

data — Table

Outputs

data — Table

Settings

The Attributes panel with a Add Column node selected.

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

The same settings, as the Assistant writes them

{
  columns: [{ name: string, kind: 'expression' | 'constant' | 'today' | 'now' | 'param', expression?: string, value?: string, valueType?: 'string' | 'integer' | 'double' | 'boolean' | 'date', paramColumn?: string }]
}

kind 'expression' = raw DuckDB SQL over the input's columns, double-quoting a name with spaces (e.g. "Price - Target", or "CASE WHEN qty > 10 THEN 'bulk' ELSE 'single' END" — a CASE may omit its END); 'constant' = a fixed value cast to valueType; 'today'/'now' = current_date/current_timestamp; 'param' = row-0 value of paramColumn from the table wired into the params port, cast to valueType (#631)

Ready-made variants

The add-node search also lists these, each adding a Add Column already set up:

PMT — Loan payment — The constant payment per period that repays a loan at a fixed rate
IPMT — Interest part of a payment — The interest portion of one period's loan payment
PPMT — Principal part of a payment — The principal portion of one period's loan payment
CUMIPMT — Cumulative interest — Interest paid between two periods of a loan
CUMPRINC — Cumulative principal — Principal repaid between two periods of a loan
ISPMT — Interest on an even-principal loan — Interest for one period of a loan whose principal is repaid evenly
PV — Present value — What a series of equal future payments is worth today
FV — Future value — What regular payments plus a starting balance grow to
NPER — Number of periods — How many payments it takes to reach a target balance
RATE — Interest rate per period — The rate implied by a payment, a present value and a term; NULL when none fits
EFFECT — Effective annual rate — The effective annual rate of a nominal rate compounded n times a year
NOMINAL — Nominal annual rate — The nominal annual rate behind an effective rate compounded n times a year
RRI — Growth rate (CAGR) — The constant rate per period that grows a starting value into an ending value
PDURATION — Periods to reach a value — How many periods an investment needs to grow from one value to another
SLN — Straight-line depreciation — The same depreciation charge every period
SYD — Sum-of-years depreciation — Depreciation for one period on the sum-of-years'-digits schedule
DB — Fixed-declining depreciation — Depreciation for one period on the fixed-declining-balance schedule
DDB — Double-declining depreciation — Depreciation for one period on the double-declining-balance schedule
DOLLARDE — Fraction to decimal price — A price quoted as a fraction (1.02 in sixteenths) as a decimal (1.125)
DOLLARFR — Decimal to fraction price — A decimal price (1.125) as a fraction (1.02 in sixteenths)
FVSCHEDULE — Future value, varying rates — A principal compounded through a column of per-period rates
NPV — Net present value — The present value of a cash-flow column at a discount rate (first flow one period out, as in Excel)
XNPV — Net present value, dated — The present value of dated cash flows at an annual rate
IRR — Internal rate of return — The rate at which a cash-flow column nets to zero; NULL when none exists
XIRR — Internal rate of return, dated — The annual rate at which dated cash flows net to zero; NULL when none exists
MIRR — Modified internal rate of return — Return with outflows financed at one rate and inflows reinvested at another
MXIRR — Modified internal rate of return, dated — MIRR over dated cash flows, compounded by calendar time

Example

You want a margin and a size label on every order line. Add margin as a Formula "Unit Price" - Cost, and size as a Formula IF qty > 10 THEN 'bulk' ELSE 'single'.

Unit Price	Cost	qty	margin	size
12.50	8.00	3	4.50	single
9.00	7.25	40	1.75	bulk

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