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 |
|
|---|---|
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 |
|
|---|---|
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 |
|
|---|---|
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 |
|
|---|---|
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.
For more information, see Working with JSON v2.
For more information, see Working with Arrays.