When you right-click on a column heading and select Add transformation, you will be taken to the transformation function editor.

The left-hand menu contains two tabs: one for transformation functions and the other for connections.

You can either scroll down to find the required function or use the search box to find it then drag and drop in the area on the right.

Example - remove special characters from phone numbers:

  1. Go to Data Explorer > Sample data source.
  2. Drag and drop the Customer V1 file into the workspace.
  3. Drag and drop the Transform step and connect it to Customer V1.
  4. In the Transform step, click View Data.
  5. Right-click on the Telephone column heading and select Add transformation.
  6. In the search field on the left type in Remove noise and click on the function when it's shown. It will appear in the right-hand side (workspace).
  7. Tick the following boxes: Remove vowels and Remove whitespace.
  8. Click Apply in the top-right corner. You will be taken back to the file view. The icon will appear next to the Telephone header indicating that values in this column have been transformed.

Expression preview

When creating/editing a function, the expression preview in the bottom-left corner allows you to see how the data will be transformed if you save your changes. If you're working on a workflow that contains a step that hasn't been executed, you have to click Execute to see the preview of the data.

You can also see how a transformation will behave by using sample/dummy data: click Single at the bottom and enter the values you want to test.

To revert the transformation, right-click on the column header and select Remove transformation.

You can either continue building on this transformation using the Workflow Designer by clicking Save as workflow or save the data as a .csv file by clicking Download as.CSV.

Transformation functions

Function Description
Add period Add a period to a date/time expression
After Extracts from an input value, everything that follows the search value
And The logical operator and: if both inputs are true it returns true
Before Extracts from an input value, everything that precedes thesearch value
Calculate Evaluate a simple mathematical calculation using +, -, /, * and %
Add Add
Divide Divide
Multiply Multiply
Remainder Remainder
Subtract Subtract
Check Tests whether a value is valid with any of a wide range of validation tests
Alphabetic Checks that the input value is alphabetic
Alphanumeric Checks that the input value is alphanumeric
Date Checks whether the value if of date data type
Decimal Checks whether the value is a decimalNote: Integers don't pass this test
Empty Checks whether the value is empty (spaces or Null)
Error Checks whether the value is drilldown error
Even Checks whether the value is an even number
False Checks if the input is a False value
Integer Checks whether the value is an Integer
Leap Year Checks that the input value is a leap year
Negative Checks that the input value is a negative number
Null Checks that the input is a null value
Number Checks that the input value is a number
Odd Tests if the value is an odd number
Positive Checks that the input value is a positive number
True Checks that the input is a true value
Valid CUSIP Checks if the input is a valid Id defined by the committee on uniform security identification procedures (CUSIP)
Valid International Securities Identification Number Checks if the input is valid International Securities Identification Number (ISIN)
Valid Stock Exchange Daily Official List Returns true if the input is valid stock exchange daily official list(SEDOL) number
Warning Checks whether the input value is a drilldown warning
Chunk Splits the input value into variable length chunks using thesupplied length, returning a list of split values
Combine lists Joins two lists of values in different ways
Difference The symmetric difference, leaving the values from the left andright that don't intersect
Intersection The intersection of the two lists leaving only values that matchon both sides
Remove left The values from the right list that don't intersect the left list
Remove right The values from the left list that don't intersect the Right list
Union The two lists combined
Common in list Analyses a list and return two values. The first is most or least common in the list, and second is how often it occurred
Least common in list Returns a list of two values by analyzing a list for the least common value. The first value on the returned list is the least common value and the second is number of times it occurs
Most common in list Returns a list of two values by analyzing a list for the most common value. The first value on the returned list is the most common value and the second is number of times it occurs
Common prefix Compares all supplied values and returns the prefix that is common to all of them
Compare Collections of expressions to perform validations. These checks are particularly useful for the text field.
Contains Checks whether the input value contains the check value, optionally ignoring case
Ends with Checks whether the input value ends with a given suffix, optionally ignoring case
Equals Checks the input value for equality with the comparison value, optionally ignoring case
Equals (standardized) Checks the input value for equality with the comparison value,based solely on appearance i.e. ignoring any differences in datatype
Equals error text Checks if the input values equal a specified error message
Equals warning text Checks if the input values equal a specified warning message
Greater than Checks if the input value is greater than the comparison value
Greater than or equal Checks if the input value is greater than or equal to thecomparison value
Less than Checks if the input value is less than the comparison value
Less than or equal Checks if the input value is equal to or less than the comparison value
Matches expression Checks if the input matches a regular expression
Matches format Checks if the input matches a format pattern
Sounds like Checks whether the input value phonetically matches the comparison value
Starts with Checks whether the input values start with a given prefix, optionally ignoring case
Compare date/time Compares two dates/times and returns the difference in unit
Concatenate Concatenates two variables to form a new single variable
Constant value Returns a specified value
Contains match Returns true if any element of a list is contained in a lookup column.

The following match types are available:
  • Case-insensitive - ignores the letter case.
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces é with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits - as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.
Note that both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation).

Three input types are available:
  • Space-separated – treat the input value as a single sentence of whitespace-separated words.
  • Whole value – treat the input value as a single sentence containing only one word (i.e. the whole value is the word).
  • Comma-separated – treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.
Convert Converts input into another datatype, as selected by the user
Auto convert Auto converts
To alphanumeric Converts any value to an alphanumeric value
To date Converts any value to a date value. For example: 25.12.2009 to 25-Dec-2009
To decimal Converts any value to a decimal value
To integer Converts any value to an integer valueFor example: Input Value-12.3 Return Value-12
To null Converts any blank spaces to null ones
To spaces Converts any null values to blank ones
To time Converts any value to a time periodFor example: Input Value:00-00-23 Return Value-00:00:23
Convert to date Converts any value to a date value. For example: 25.12.2009 to 25-Dec-2009
Create date/time stamp Creates a time stamp with supplied value year, month, day, hours, minutes, secondsFor example: Year-2009, Month-12, Day-25, Hours-12, Minutes40, Seconds-30to 25-Dec-2009 12:40:30
Create list Creates a value list from any number of values
Current row Returns the row ID, starting at 1, i.e. the current row is displayed
Current timestamp (dynamic) Returns the current timestamp
Datatype Returns the datatype of the input value
Decode geohash coordinates Decodes a geohash to its latitude or longitude component
Delimited substring Returns the substring of a value using a start delimiter or an index position and either an end delimiter or return value length. For example:Input value: ABC,DEF, GHIDelimiter:,Length:5Returns value: DEF,G
Difference Returns the portion that is a difference between two values
Edit distance Calculates the Levenshtein edit distance between the input value and the compare value returning the number of edits required to transform the input value to the compare value and vice versa. For example, the edit distance between Frank and Plank is 2, because two characters must be changed to go from Frank to Plank and vice versa. Edits are not just transpositions but also removals and insertions.
Edit distance percentage Calculates the Levenshtein edit distance between the input value and the compare value returning a percentage similarity between the two values
Jaro edit measure Calculates the Jaro-Winkler distance between the input value and the compare value returning a percentage match. The higher the match the more similar the values are.
Jaro-Winkler edit measure Calculates the Jaro-Winkler distance between the input value and the compare value returning a percentage match. The higher the match the more similar the values are. The Jaro-Winkler algorithm is the variant of Jaro algorithm and includes additional checks which test for a common prefix at the start of both the values.
Error message Returns an error message, setting the expression into error and displaying the result in red.
Escape Escapes or un-escapes a string based on a given escaping style.
Example phone number Generates an example phone number for a specified region in various formats
Expand list Extract all members of a list and separate them with a specified delimiter
Extract matches as list Extracts elements from the input that are contained in a lookup column and outputs them as a list.

The following match types are available:
  • Case-insensitive - ignores the letter case.
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces é with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits - as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.
Note that both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation).

Three input types are available:
  • Space-separated – treat the input value as a single sentence of whitespace-separated words.
  • Whole value – treat the input value as a single sentence containing only one word (i.e. the whole value is the word).
  • Comma-separated – treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.
Extracts from list Parses a value that is a list of comma separated values allowing any value to be extracted from the list by given index number
Extracts timestamp element Extracts a defined element from a date/time value
Get age Age
Get century Century
Get century Get a defined week from a date/day value
Get day of week name Get a defined name from a date/day value
Get days Days
Get hours Hours
Get millis Milliseconds
Get minutes Minutes
Get month name Get a defined name from a date/month value
Get months Months
Get seconds Months
Get weeks Weeks
Get years Years
First non-null Given a variable number of input values, it returns the first valuethat is not null (empty)
Format phone number Properly format a phone number or extract a specific attribute from it
Format date Formats a date/time with custom date format
Geohash coordinates Converts latitude and longitude to a geohash
Get cell Returns a value from a cell at a given row/column position from the source of current drilldown
Hash code Returns a generated MD5 hash code for the input value
If then else Checks the condition field for the value of true. If its true returns the condition met or else. Can also have multiple condition fields to be evaluated if a precursory condition is not met
In length range Checks whether the input is within the specified start and end length range
In range Checks whether the input is within the specified start and end range, optionally ignoring case
Index of difference Returns the position where two values begin to differ, starting from 1 or 0 if there is no difference
Initials Returns the initials by splitting words from the input value with an optional list of delimiters (default is white space)
Insert Insert a value into the input value at a given position
Length Returns the number of characters in the given string
List Transform a list of values using a variety of operations. Multiple operations can be used in a cumulative manner.
Common Prefix Compares all supplied values and returns the prefix that is common to all of them
Deduplicate Deduplicate all values in a list
Reverse Reverse all values in a list
Reverse sort Reverse sort all values in a list
Sort Sort all the values in a list
List frequency Returns unique values in the list interleaved with their count (frequency)
Lookup Looks up values in a specified column in the lookup table and applies an operation based on a selected column for each of the matching rows. Note that this function replaces and deprecates Lookup List and Lookup Value functions.

Where the value in the return column is numeric (integer or decimal), the applied operations include aggregation operations (e.g. maximum or average) on the specific return column for those values on the rows where the lookup value appears in the lookup column.

For non-numeric values, operations include getting the first matched value or returning a list of all the values.

The Aggregation Type option allows you to define what actions to perform on the selected Aggregate Columns. These are the supported types:
  • All Values - returns a comma-separated list of all values in the Aggregate Column where the Lookup Value has a match in the Lookup Column. If the 'Distinct' checkbox is selected, this list is de-duplicated.
  • First Value - returns the first value (from the row with the lowest row count) in the Aggregate Column where the Lookup Value has a match in the Lookup Column.
  • Last Value - returns the last value (from the row with the highest row count) in the Aggregate Column where the Lookup Value has a match in the Lookup Column.
  • Minimum Value
  • Count
  • Count (excluding null)
  • Sum
  • Average
  • Standard Deviation
  • Standard Deviation (Population)
  • Variance
  • Variance (Population)
  • Median
You can also define how the matching is carried out by selecting one of the available Match Types:
  • Exact Match - returns matches where there's an exact match to the lookup value (case sensitive).
  • Ignore datatype - returns matches where there's a match to the lookup value, ignoring the datatype of the value in the lookup column.
  • Case-insensitive - returns matches where there's a match to the lookup value, ignoring the case of the value in the lookup column.
  • Denoise - denoising values transforms them by translating all letters to uppercase and retaining only letters and digits. This option returns all matches where the denoised lookup value matches the denoised value in the lookup column.
  • Format - returns all matches where the format of the lookup value matches the format of the value in the lookup column.
  • Soundex - returns all matches where the Soundex of the lookup value matches the Soundex of the value in the lookup column.
  • Metaphone - returns all matches where the metaphone of the lookup value matches the metaphone of the value in the lookup column.
  • Double Metaphone - returns all matches where the double metaphone of the lookup value matches the double metaphone of the value in the lookup column.
Lookup check Looks up values in a specified column in a lookup table and checks whether those values exist in the lookup column. Returns True or False only.
Lookup count Looks up values in a specified column in a lookup table and returns a count for the number of times a given value occurs in the lookup column. Returns an integer value of 0 or greater.
Multi compare Performs multiple compare operations in one transformation. Returns true if any of the individual arguments are true.
Contains Checks whether the input value contains the check value
Ends with Checks whether the input value ends with a given suffix
Equals Checks the input value for equality with the comparison value
Equals (standardized) Checks the input value for equality with the comparison value(s) based solely on appearance i.e. ignores differences in datatype.
Equals error text Checks if the input value equals a specific error message
Equals warning text Checks if the input value equals a specific warning message
Greater than Checks if the input value is greater than the comparison value
Greater than or equal to Checks if the input value is greater than or equal to the comparison value
Less than Checks if the input value is less than the comparison value
Less than or equal to Checks if the input value is less than or equal to the comparison value
Matches expression Checks if the input value matches a regular expression
Matches format Checks if the input value matches a format
Sounds like Checks if the input value phonetically matches a comparison value
Starts with Checks if the input value starts with any given prefix
Not Turns the input value from true to false or vice-versa
Or The logical operator or; if either input is true it returns true
Pad Pads any value with a chosen character to achieve a given overall length, with the option of putting the padding before or after the given value.
Partition first Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the 1st occurrence of the fragment and 'after' is what's left. A fragment is a regularexpression.
Partition last Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the 1st occurrence of the fragment and 'after' is what's left. A fragment is a regularexpression.
Parse Parses a value, extracting other values which match one of the supplied formats. This can be used to extract values that look like an expected type of value from a free-text value. For example, an embedded postal code in an address string. The result can be deduplicated, comma separated quoted list of values for each result found. Multiple patterns can be searched at the same time and they are searched in sequence.
Parse by format Example: if the input value is 'abc1234def5678' and the format pattern to search for is '9999', then the result will be '1234,5678'.
Parse by regular expression Example: if the input value is 'abc1234abc5678' and the expression to search for is 'a.c', then the result will be 'abc,abc'.
Power of n Returns the nth power of the input value
Position Returns the first index position, starting from 1, of a search value within the input value
Phone number match Tests if two numbers represent exactly the same number
Phone number match code Compares two numbers and returns the match code
Remove matches (lookup function) Removes elements from the input that are contained in a lookup column.The following match types are available:
  • Case-insensitive - ignores the letter case.
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces é with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits - as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.
Note that both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation).

Three input types are available:
  • Space-separated – treat the input value as a single sentence of whitespace-separated words.
  • Whole value – treat the input value as a single sentence containing only one word (i.e. the whole value is the word).
  • Comma-separated – treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.
Remove noise Returns the input value after standardizing it by removing noise from the value. Initially, the value is transformed by translating all letter to uppercase and retaining only letters and digits.
Repeat Returns an input value repeated a defined number of times.Example:Input Value: ABCRepeat: 3Return Value: ABCABCABC
Replace Replaces all the instances of the search value with the replacement value. Example:Using the input value 'ABC:DEF:GHI', the search value ':' and the replacement value ';' will return 'ABC;DEF;GHI'
Regular expression replace Replaces all the instances of the search value with the replacement value. Example:Using the input value 'ABC::DEF:::::::::GHI', the search value ':+' and the replacement value ';' will return 'ABC;DEF;GHI'

This differs from the 'replace' function as the search value can be a regular function as opposed to a constant value.
Replace first Replaces first the instances of the search value with the replacement value. Example:Using the input value 'ABC:DEF:GHI', the search value ':' and the replacement value ';' will yield the result 'ABC;DEF:GHI'
Replace matches (lookup function) Replaces elements from a list with matching elements from a lookup table.The following match types are available:
  • Case-insensitive - ignores the letter case.
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces é with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits - as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.
Note that both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation).

Three input types are available:
  • Space-separated – treat the input value as a single sentence of whitespace-separated words.
  • Whole value – treat the input value as a single sentence containing only one word (i.e. the whole value is the word).
  • Comma-separated – treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.
Row count Returns the total number of rows in a view
Round number Sets the number of decimal points after a decimal value
Split Splits a value using another value in the field separator
Strings between Searches a value for substrings delimited by a start and end tag, returning all matching substrings in a list
Strip substring Strips the supplied substring value from the given input value if present. Example:If input value is 'abcdefghi' and search value is 'def', the result would be 'abcghi'.
Substring Returns substring of a given value specified by giving start and end positions
Tag a value Adds the start and end value to the input value if they are not present in their respective positions. Example: Input value: 12345> Start Value: < End Value: > Return value: <12345>
Transform number Transforms a number using any of a wide selection of mathematical and scientific conversions
Absolute Returns the absolute value of the integer
Arccosine Returns the trigonometric arc cosine of the input as an angle in radians, in the range of 0 through to pi
Arcsine Returns the trigonometric arc sine of the input as an angle in radians, in the range of -pi/2 through to +pi/2
Arctangent Returns the trigonometric arctangent of the input as an angle in radians, in the range of -pi/2 through to +pi/2
Cosine Returns the trigonometric cosine of an angle in radians
Cube Returns the cube of a number
Cube root Returns the cube root of a number
Euler raised Returns Euler raised, e, raised to the power of input value
Log base 10 Returns the logarithm of any number, to base 10
Natural log Returns the natural logarithm of any number, which is the log to base e
Negate Converts a positive number to negative and vice-versa
Precision Returns a numerical precision of input value - a number of significant digits
Random integer Returns a random integer between 1 and the inputted value
Random number Returns a random number between 0 and the supplied maximum value
Scale Returns a numeric scale of a value - a number of significant digits after the decimal
Sine Returns the trigonometric sine of an angle in radians
Square root Returns the square root of a number
Sum digits Returns a sum of numeric digit of any value. Example:
Input value: 546.2
Sum digits: 17
Tangents Returns the trigonometric tangent of an angle in radians
Transform text Transform a text value using a variety of operations. Multiple operations can be used in a cumulative manner.
Compact spaces Reduces multiple spaces in input value to single space
Double Metaphone Implement double Metaphone algorithm developed by Lawerence Phillips
Double Metaphone (alternate) Implement alternate double Metaphone algorithm developed by Lawerence Phillips
Extract integer Extract all values that are numbers out of embedded space or letters. Contiguous integers are extracted.
Fingerprint Returns a fingerprint of an input value
Format pattern Returns a format pattern of an input value
From hex to text Converts a hexadecimal input into a string
From text to hex Converts a string input into its hexadecimal value
From Unicode to text Converts a Unicode input to a string
From text to Unicode Converts an input string into its Unicode value
Improved Metaphone Implements the improved double Metaphone algorithm
Improved Metaphone (alternate) Implements the improved double Metaphone algorithm for alternate value
Lowercase Converts to lowercase
Refined Soundex Implements refined Soundex algorithm
Remove noise Returns the input value after standardizing it by removing noise from the value. Initially, the value is transformed by translating all letter to uppercase and retaining only letters and digits.
Remove unprintable characters Removes unprintable characters in the input value
Remove whitespace Remove whitespaces in an input value
Remove simple format pattern Simple format pattern is just like a format pattern, just that, it reduces multiple format characters to just one. Example:
Format: AAAAA9999AA
Simple format: A9A
Soundex Soundex is an encoding used to relate similar names, but can also be used as a general-purpose scheme to find words with similar phonemes
Standardize Standardizes a value by removing leading, embedding & trailing spaces, converting white space and punctuations to a standard space, except for quote & period characters which are removed. Digits are preserved, letters are preserved as they are lower case, Unicode normalized version. This function is used for comparing values in a domain.
Strip accents Removes diacritics (~= accents) from a value
Title case Converts to title case
To proper case Converts to proper case where the first letter is in uppercase whereas all others are in lower case
Uppercase Converts to uppercase
Trim Removes all the instances of a single character from the start of the input value
Unquote value Removes the quote value from the start and/or end of the input value, but leaves the respective part of the value alone if the quote is not present. Example:
Input value: ABC12345ABC
Value to remove (case sensitive): ABC
Returns value: 12345
Validate phone number Validates a phone number according to a specific test
Warning message Returns a warning message and displays the result in yellow