Transformations

Professional spaces can use the Transformations located in Modeler Prepare to make additional modifications to the data.

This table lists all available Transformations and describes their functions. For more details about specific Transformations, view the related topics in this section.

Note: Some transformations do not have associated topics.

This table shows all available Transformations and describes their functions:


Transformation Type	Description	Steps
Add Column Transformation	You can add a new column to the dataset by writing a BQL (Birst Query Language) expression against the Birst Data Store. You can select the data type for your column from these options: Text Numeric Decimal Precise Decimal Date Date and Time	Create Prep Source. Add Step. Select Add Column transforms. Enter a name for the new column. Write the BQL expression as required. Select the data type for the column. Apply.
Append Table Transformation	You can combine data from a source by appending the data according to selected matching columns. This transformation includes these functions: Remove Duplicates This function removes duplicate records from the appended data to create a clean dataset. Handle Unmapped Values This function manages values in the data that do not match the target structure. You can handle these values or flag them for review. Add Join You can specify how to combine the source data with existing data. Define the relationship between datasets to support complex data integration based on join conditions.	Create the Prep Source. Add a step. Select Transforms > Append Table. Select the Source. Select a column from the Prep Source table. Select a column from the Target Source table. Select another column from the Prep Source table. Type a default value for unmapped values. Select another column from the Target Source table. Add join for additional conditions. Apply the changes.
Combine Transformation	You can merge two or more columns into one, with or without a delimiter. These are the supported delimiters: Custom Comma Space Hyphen Semicolon Underscore Slash	Create Prep Source. Add Step. Select Columns. Select Combine transform. Add further column to combine. Select a delimiter for combining columns, or specify your own in the custom options. Apply.
Custom Transform	You can create a custom transform by writing a ETL script	Create Prep Source. Add Step. Select Add Custom Transform. Add or edit the output column. Add or edit the script. Validate and click Save.
Data Type Transformation	You can change the data type of columns. This change does not affect the source data, it applies only in Birst. In addition to standard date casting, you can now suggest date formats to parse and cast columns of Numeric and Text types into Dates.	Create Prep Source. Add Step. Select the columns for which you would like to change the data type. Select Data Type from Transforms. Select required data type. Apply.
Date Transformation	You can perform a variety of date-related functions. The system creates a new column with the applied function. Date Extract You can use this function to extract specific components from a date, such as the year, month, or day. Date Add You can use this function to add a specified amount of time, such as year, quarter, month, day, or hour, to a given date. Date Subtract You can use this function to subtract a specified amount of time from a given date. Difference with Now This function calculates the difference between a specified date and the current date and time. Difference This function calculates the difference between two specified dates and returns the result in a chosen unit, such as year, quarter, month, day, or hour. These functions work only with columns of type date or datetime. If you apply them to other column types, the system returns an error.	Create Prep Source. Add Step. Select the column to which you would like to apply the function, having Date as data type. Select Date from Transforms. Choose function. Set required date. Apply. A new column is added with applied function.
Filter Transformation	You can filter values in one or more columns using various conditions. To filter values, select the column name and apply an IF condition using one of these options: Equal to Not equal to Greater than Greater than or equal to Less than Less than or equal to In Not in Is missing Is not missing Similar to Not similar to After selecting the required condition, you must add the filter value. Additionally, the functionality supports logical conditions, such as AND and OR when filtering across two or more selected columns. This enables you to create more complex queries between them.	Create Prep Source. Add Step. Select columns on which you would like to apply the transform. Select Filter from Transforms. Select column name from drop down. Select IF condition from dropdown. Few conditions might be not applicable based on data type of the column. Add filter Value. For multi-column filtering, complete these steps for adding the filter to that particular column and add additional condition between the columns, such as AND or OR. Apply.
Ignore Transformation	You can exclude the column from the Birst data store, but it is not a change of the column in the source.	Create Prep Source. Add Step. Select columns you want to ignore. Select Ignore from Transforms. Apply.
Index Transformation	You can create a new column that generates ascending sequential numbers. Additionally, there is an option called 'Provide Start' that enables you to customize the starting point for your indexing.	Create Prep Source. Add Step. Select Index from Transforms. Specify start with value. Apply.
Lookup Transformation	You can create a new column based on a lookup value in another table. A lookup is a common operation that analysts use for data cleansing. Lookups in BI tools are similar to functions, such as VLOOKUP and HLOOKUP in Excel. The VLOOKUP function matches a key value in one column and returns the corresponding value from another column. If there are duplicates, the system considers the first match. If no match is found, the function returns a specified default value.	Create Prep Source. Add Step. Select Lookup transform. Choose the source table for the lookup. Select the column that contains the matching lookup value. Specify the column that you want to include in the Lookup. Apply.
Merge Transformation	You can merge two sources based on the selected join type and add a condition: Select join type Inner join: Combines rows from both sources where a match exists. Outer join: Combines all rows from both sources, filling in gaps with NULLs where no match exists. Right join: Returns all rows from the right source and matched rows from the left source. Left join: Returns all rows from the left source and matched rows from the right source. Add additional condition (optional) You can use AND to specify that both conditions must be true for the rows to be included in the result. You can use OR to specify that at least one of the conditions must be true for the rows to be included in the result.	Create Prep Source. Add Step. Select Merge from Transforms. Select Source. Select Column from ‘Prep Source’ Table. Select Column from ‘Target Source’ Table. Select Join Type: Inner Join, Outer Join, Right Join, Left Join. Add additional Condition, such as AND or OR, which is Optional. Apply.
Null Handling Transformation	You can replace empty cells in a column with a text of your choice.	Create Prep Source. Add Step. Select columns on which you would like to apply the transform. Select Null Handling from Transforms. Enter replace with value. Apply.
Pivot Transformation	To pivot a table based on a specific column, you must use the GROUP BY clause. Select the column to aggregate the data.	Create Prep Source. Add Step. Select the column. Choose the column that you want to group by. Specify the column for the values you want to aggregate. Apply.
Remove Duplicates Transformation	Deletes records based on the contents of one or more columns. When employing this transformation, the first matching record is kept while the other matching records are removed from the prepared data source.	Create Prep Source. Add Step. Select columns. Select Remove Duplicates transform. If you want, you can add one or more columns to remove duplicates. Apply.
Remove Records Transformation	You can remove records based on specific values in one or more columns: Select the column that contains the values you want to filter. Apply a condition using the IF option. Available conditions include: Equal to Not equal to Greater than Greater than or equal to Less than Less than or equal to In Not in Is missing Is not missing Like Not like Specify the filter value that matches the condition. You can also apply logical operators, such as AND or OR to combine conditions across multiple columns. The system removes all records that match the selected conditions.	Create Prep Source. Add Step. Select columns on which you would like to apply the transform. Select Remove Records from Transforms. Select column name from drop down. Select If condition from dropdown. Few conditions might be not applicable based on data type of the column. Add filter Value. For multi-column filtering, complete these steps for adding the filter to that particular column and add additional condition between the columns, such as AND or OR. Apply.
Rename Transformation	You can rename the title of the selected column or columns.	Create Prep Source. Add Step. Select the columns you want to rename. Select Rename from Transforms. Specify the new name for the selected column. Apply.
Replace Transformation	You can find and replace a value in selected columns. Additionally, to replace multiple words simultaneously, you can use the 'add' function. This function enables you to list several words to be replaced along with their corresponding replacements in one step.	Create Prep Source. Add Step. Select columns on which you would like to apply the Transform. Select replace from Transforms Specify values for find and replace with. Select ADD (optional) to replace multiple words simultaneously. Apply.
Split Column Transformation	You can split a column into two or more columns by either specifying a delimiter for splitting, or you must provide a fixed length of characters. The delimiters supported for splitting are: Custom Comma Space Hyphen Semicolon Underscore Slash	Create Prep Source. Add Step. Select a column. Select Split Column transform. Select the delimiter on which you want to split in delimiter option or specify the number of characters in the fixed length option. Apply.
Split Row Transformation	You can duplicate a column based on a selected delimiter. The delimiters supported for splitting are: Custom Comma Space Hyphen Semicolon Underscore slash	Create Prep Source. Add Step. Select a column. Select Split Row transform. Select the delimiter on which you want to split the row. Apply.
Summarize Transformation	You can group the values of multiple rows into single value. The summary is based on the selected columns and applies aggregation to the specified value column. The aggregation options supported are: Avg Count Max Min Sum .	Create Prep Source. Add Step. Select columns. Select Summarize transform. Add columns for summarization. Add the value column upon which transformation will be done. Select the aggregation type. Apply.
Text Transformation	You can changes case or trims out empty spaces of the selected column. Provides uppercase and lowercase transforms, and trims extra blank spaces.	Create Prep Source. Add Step. Select the columns you would like to modify. Select Text from Transforms. Choose Uppercase/Lowercase/Trim. Apply.