Shape · Columns

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.

Also known as: Number format, display format

How it works

Under Rules, + Add rule formats every column whose name matches a pattern under Column name matches (* any run, ? one character) and whose type is picked under Column types. Under Column formats, pick a column and choose Display as: Number, Currency, Percent, Date or Text. Numbers take decimals, a thousands separator and a fixed total width; currency takes an ISO code such as USD shown before or after the value; Percent multiplies by 100, so a 0 to 1 fraction prints as 42%, unless you tick Values are already 0–100; Accounting negatives — (1,234) swaps the minus sign for parentheses; a unit such as kg can sit before or after; dates take a Date format mask such as DD/MM/YYYY. Alignment, width, Bold and Hide from reports shape the table, and If it can't apply says what to do with a value the format does not fit. The settings travel with the column downstream, and Save as a project rule applies the same format to every matching column in the project. To change the data rather than its look, use Format to Text.

Inputs and outputs

Settings

{
  formats: [{ column: string, type?: "number"|"currency"|"percent"|"date"|"text", decimals?: number, totalDigits?: number (fixed width: integer + decimal digits; needs decimals set), separator?: "comma"|"dot"|"space"|"none", currencyCode?: string (ISO 4217, e.g. "USD" — preferred over symbol), currencyDisplay?: "symbol"|"code", symbol?: string, symbolPosition?: "prefix"|"suffix", negative?: "minus"|"parens", unit?: string, unitPosition?: "prefix"|"suffix" (default suffix), alreadyScaled?: boolean ("percent" multiplies by 100: a 0-1 fraction like 0.42 prints "42%"; set true ONLY when the column already holds 0-100 percent points like 42 — never format a *100 computed column without it), mask?: string (e.g. "DD/MM/YYYY"), bold?: boolean, align?: "left"|"center"|"right", width?: "narrow"|"wide", hidden?: boolean }]
}

Example

A sales summary feeds a report. Format revenue as Currency with code USD and 2 decimals, share (a 0 to 1 fraction from Group Aggregate) as Percent with 1 decimal, and hide the region_id helper column. The numbers underneath stay numbers, so a total further down still adds up.

More in Columns

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