Skip to main content

Create New Column

You can create a new column by adding or editing a formula on any existing column.

New Formula

The New Formula transformation allows you to create a new column based upon a formula that you provide to the transformation. Below are some examples.

Add a column of text values

You can insert a new column containing a string value that you specify as part of the transformation. In the following example, the status column is created, and all values in it are set to ok.

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

'ok'

Parameter: New column name

status

Add a column that uses a function

You can insert a new column by using a function. In the following example, the currentyear column is extracted as a new column from the TransactionDate column using YEAR function.

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

YEAR (TransactionDate)

Parameter: New column name

currentyear

For more information on extracting date information, see Extract Values.

Add a column that references another column

You can also insert columns containing references to other columns. In the following example, the totalCost column is created called totalCost, which is based on the formula using three separate columns: baseCost + totalTax - totalDiscount:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

baseCost + totalTax - totalDiscount

Parameter: New column name

totalCost

Add a column using constants, functions, and column references

You can insert a column by using nested expressions by using constants, functions, and column references. In the following example, the Three column is created, which is based on nested functions ROUND and DIVIDE.

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

ROUND(DIVIDE(10,3),0)

Parameter: New column name

Three

Merge Columns

You can merge two or more columns together to create a new column containing the merged values. For more information, see Add Two Columns.

Extract Values from a Column

You can extract values based on patterns or literal values from one column and insert them into a new column. See Extract Values.

Split Column Values

You can split the values in a column into separate columns based on delimiters and other conditions that you define. See Split Column.

Convert a Column into Multiple Columns

Unnest

You can extract values stored in an array into separate columns in your dataset. This type of transformation can be useful for unpacking nested data such as JSON into tabular format.