Shape · Columns

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.

Also known as: Mapping table, recode, translate codes

How it works

Wire the table to change into data and the code list into mapping. Under Columns to replace values in pick the data columns; Find (mapping column) and Replace with (mapping column) name the two mapping columns, or keep the default First column and Second column options to use the mapping's first and second column. A value equal to a find value becomes its replacement, compared as whole values; a replacement of null gives null. Anything else passes through unchanged unless Fail when a value has no match is ticked, in which case an unmatched value, a null included, stops the run and names the value. The run also fails if the find column holds a null or the same value twice. Rows and their order are kept one for one, and an empty mapping only warns.

Inputs and outputs

Inputs

data — Table
mapping — Table

Outputs

data — Table

Settings

The Attributes panel with a Replace via Mapping node selected.

The Attributes panel of Replace via Mapping, 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: string[]
  fromColumn?: string
  toColumn?: string
  failOnUnmatched?: boolean
}

input `data` = the table whose `columns` change; input `mapping` = from → to pairs (fromColumn / toColumn; empty = the mapping's first / second column). A value equal to a from value becomes its to value (a NULL to gives NULL); unmatched values pass through unless failOnUnmatched. Fails on a NULL or repeated from value. Rows and row order are kept.

Example

Orders carry two-letter state codes and the report wants names. Wire a states table with columns code and name into mapping, pick ship_state and bill_state, leave the mapping columns blank, and tick Fail when a value has no match to catch a typo in the data.

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