Check Values

After setting data types, you should address any missing or mismatched values in the column. For example, if you change a column's data type from Decimal to Integer, values that contain decimal points may be reported as mismatched values. Use the ROUND function to round them to the nearest integer.

Add One Column into Another

To perform math operations, you can use the set transform to update values in a column based on a math operation. The following transform multiplies the column by 10 and adds the value of colB:

Transformation Name

Edit column with formula

Parameter: Columns

colA

Parameter: Formula

((colA * 10) + colB)

All values in colA are modified based on this operation.

Add Selective Values from One Column into Another

You can use the set transform to perform math operations based on a condition you define. In the following step, the Cost column is replaced reduced by 10% if the Qty column is more than 100. The expression is rounded down to the nearest integer, so that the type of the column (Integer) is not changed:

Transformation Name

Edit column with formula

Parameter: Columns

Cost

Parameter: Formula

IF(Qty > 100, ROUND(Cost * 0.9), Cost)

For rows in which Qty is less than 100, the value of Cost is written back to the column (no change).

Add Two Columns into a New Third Column

To create a new column in which a math operation is performed on two other columns, use the New Formula transformation. The following multiplies Qty and UnitPrice to yield Cost:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

MULTIPLY(Qty,UnitPrice)

Parameter: New column name

'Cost'

Working with More than Two Columns

If you need to work with more than two columns, numeric operators allow you to reference any number of columns and static values in a single expression.

However, you should be careful to avoid making expressions that are too complex, as they can be difficult to parse and debug.

Tip: When performing complex mathematic operations, you may want to create a new column to contain the innermost computations of your expression. Then, you can reference this column in the subsequent step, which generates the full expression. In this manner, you can build complex equations in a way that is easier to understand for other users of the recipe. The final step is to delete the generated column.

Concatenating Columns

If you are concatenating string-based content between multiple columns, use the Merge Columns transformation The following creates a third column with a dash between the values of the two source columns:

Transformation Name

Merge columns

Parameter: Columns

ColA, ColB

Parameter: Separator

'-'

Parameter: New column name

ColC

Tip: This method can be used for columns of virtually any type. Change the data type of each column to String and then perform the merge operation.