Transform Data Step

Transforms attribute values using the ONE expression language.

This step is available for all transformation types: standalone plans, embedded plans, transformation rules, and transformation catalog items.

Overview

The Transform Data step modifies existing attribute values in place. Use this step when you need to clean, standardize, or reformat data without creating new columns.

Use this step to:

Standardize text formats (uppercase, lowercase, trim whitespace).
Convert data types (string to number, date formatting).
Clean and normalize values.
Apply business rules to existing data.
Fix data quality issues.

Configuration

Transform an attribute

In the step configuration, select Add transformation.
Configure the transformation:
- Attribute: Select the attribute you want to transform from the dropdown. Only attributes from connected input steps are available.
- Expression: Define how to transform the value using a ONE expression. The expression result replaces the original value.
Select Add transformation again to transform additional attributes.

Transformations overwrite the original values. If you need to preserve the original data, use the Add Attributes step instead to create a new attribute with the transformed value.

Expression examples

Standardize to uppercase

upper(country_code)

Converts country codes to uppercase for consistency.

Trim whitespace

trim(customer_name)

Removes leading and trailing spaces from text.

Format phone numbers

substituteAll(@"[^0-9]", "", phone)

Removes all non-numeric characters from phone numbers.

Standardize date format

toString(toDate(date_string, 'dd/MM/yyyy'), 'yyyy-MM-dd')

Converts dates from one format to another.

Replace null values

coalesce(category, 'Uncategorized')

Replaces null values with a default.

Round numeric values

round(price, 2)

Rounds prices to two decimal places.

Conditional transformation

iif(status = 'active', 'A', iif(status = 'inactive', 'I', 'U'))

Maps verbose status values to codes.

Common transformation patterns

Data cleansing

Remove special characters

substituteAll(@"[^a-zA-Z0-9]", "", product_code)

Standardize boolean values

iif(lower(active) in {'yes', 'y', 'true', '1'}, true, false)

Fix encoding issues

replace(replace(name, 'Ã©', 'e'), 'Ã¶', 'o')

Data standardization

Capitalize names

capitalize(lower(customer_name))

Standardize country codes

upper(trim(country))

Normalize email addresses

lower(trim(email))

Data type conversion

String to integer

toInteger(quantity_string)

Number to formatted string

toString(price, '#,##0.00')

Best practices

Test before applying: Use expression testing to verify your transformation logic with sample data before running the full transformation.
Consider null handling: Decide how null values should be handled. Use coalesce() or isnull() to provide defaults or handle nulls explicitly.
Validate data types: Ensure your expression output matches the attribute’s data type. Type mismatches can cause errors or data loss.
Document complex logic: For complex transformations, consider adding comments to the plan description explaining the business logic.
Chain transformations carefully: When multiple transformations depend on each other, ensure they execute in the correct order.

Comparison with Add Attributes step

Aspect	Transform Data	Add Attributes
Purpose	Modifies existing attribute values	Creates new attributes
Original data	Overwritten (values replaced)	Preserved (new columns added)
Use case	Clean or standardize existing values	Derive new values while keeping originals

Aspect

Transform Data

Add Attributes

Purpose

Modifies existing attribute values

Creates new attributes

Original data

Overwritten (values replaced)

Preserved (new columns added)

Use case

Clean or standardize existing values

Derive new values while keeping originals

Troubleshooting

Transformation returns null

Cause: Expression error or type mismatch.

Solution: Test the expression with sample data. Check that input values match expected types and handle edge cases.

Data type errors

Cause: Expression result doesn’t match attribute type.

Solution: Use explicit type conversion functions (toString(), toInteger(), toFloat()) to ensure correct output type.