Shape · Columns

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.

Also known as: Apply to many columns

How it works

Pick the Columns by hand or take every text or numeric column, then write one Formula in Add Column's dialect with [_CurrentField_] standing for the column being computed. Output is Overwrite, which replaces each column in place and keeps the column order, or New columns, which writes one new column per source right after it, named with a Prefix or Suffix you must fill in. Output type is Keep formula type by default or a cast to Text, Integer, Double, Boolean or Date. If the formula fails to bind on one column, the error names that column.

Inputs and outputs

Settings

{
  columns: string[]
  allTextColumns?: boolean
  allNumericColumns?: boolean
  expression: string
  mode?: "overwrite"|"new"
  affixPosition?: "prefix"|"suffix"
  affix?: string
  outputType?: ""|"string"|"integer"|"double"|"boolean"|"date"
}

Example

Six net price columns need a gross version. Pick the six columns, enter ROUND([_CurrentField_] * 1.23, 2), set Output to New columns with a Suffix of _gross, and each price_x gets a price_x_gross beside it.

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