List of Transforms

A Full list of transforms is as follows

Multi-Table Transforms

These would normally be done in the main transform because they affect more than one table

Transform	Purpose
CopyTable	Copies structure and/or data from an existing table into a new table.
JoinTables	Performs an inner or left join between two tables and stores the result in a new table
MergeTables	Merges multiple tables into one in a UNION-like manner
RemoveTable	Removes an in-memory table from the processing context, freeing up memory
SetContext	Allows the selecion of the Active tables in the global transform

Table Transforms

These only affect the active table(s). Normally, the action only applies to one table so they would be done in the LoadTable transform

Transform	Purpose
AddColumn	Adds a Column to the table
AggregateTable	Aggregate data in a table using common aggregation functions like SUM, COUNT, MAX, MIN, AVG or STRING_AGG
CleanTable	Performs common cleaning operations on all columns and rows of a table such as whitespace trimming and removal of empty columns and rows
CopyColumn	Creates a new column that is a copy (duplicate) of an existing one
GenerateHash	Generates a hash of the specified columns and stores it in a new column
GenerateID	Generates unique a ID in a column. The IDs can either be integer or GUID
PivotColumns	Reshapes long data to a wide format by pivoting a column containing column headers and a column containing column values
RemoveColumn	Removes a column from a table
RemoveDuplicates	Removes duplicate rows in a table based on all or subset of key columns
RemoveRowsByFilter	Removes rows based on a filter
RemoveRowsByRowNumber	Removes rows based on range of row numbers
RenameColumn	Renames an existing column
RenameColumnsByLookup	Renames columns in a data-driven way based on mappings in a lookup table. This is very useful for multi-language support
SetColumnDataType	Ssets the datatype of one or more columns so that invalid values are not allowed
SplitCellsToRows	Creates new rows based on newline (default) or defined separator
SplitColumnByRegEx	Splits a column based on capture groups (named or indexed) in a regular expression
TransposeTable	Transpose a table by switching rows and columns. Rows become columns, columns become rows
UnpivotColumns	Unpivots one or more columns in a table (wide to long)
UnpivotSimple	Unpivots one or more columns in a table (wide to long)
ValuesToColumns	Gets a column name from the row data and populates it with a value from the row data

Text Transformations

Transform	Purpose
ChangeCase	Changes the case of text values in a column
FindReplace	Find and replace text in a column or whole table
ParseJson	When a cell contains some JSON data, parses the JSON object and retrieves the desired property

Date Transformations

Transform	Purpose
DateAdd	Add time to or subtract time from date values in 1 or more columns
DateDiff	Calculates difference between dates
FormatDate	Formats dates using a custom string format
SplitDate	Splits a date field into separate Year, Month and Day columns

Mathematical Transformations

Transform	Purpose
Round	Round numerical data, very similar to Excel’s ROUND function

Lookups

Transform	Purpose
DbLookup	Translates values in a column similar to the way Excel VLOOKUP works, except that the lookup uses a database
HierarchyLookup	Allows to do a lookup on multiple columns where a hierarchical link exist between values. More information is available here
MartLookup	Translates values in a column similar to the way Excel VLOOKUP works, except that the lookup uses an existing mart table
TableLookup	ranslates values in a column similar to the way Excel VLOOKUP works, except that the lookup happens in an in-memory pipeline table

Special Transformations

Transform	Purpose
CustomScript	Allows creation of custom C# script to perform transforms