Transformation Operations

Updated: December 4, 2023

    Transformation operations are used to modify or update data that is provided in your streams. The system allows you to create multiple transformations within a single stream that help to define the output for generated batches. Transformation operations for each batch can be performed within the batch details interface.


    To use transformations, you need to install the CloudBlue Standard Transformations Library extension:

    Transformation Types

    Split columns 

    This transformation function allows you to divide values of one column into separate columns, using regular expressions.

    For example, you can split the word1-word2 cell value into word1 and word2, using the (.*)-(.*) regular expression. 

    Manual transformation 

    This transformation function allows you to perform manual operations that cannot be automated using other transformations.  

    For example, you can pass Item name as an input column and have Item type returned as the output column which you will need to fill in manually. 

    Lookup CloudBlue subscription data 

    This transformation function allows you to get an extended Cloudblue subscription data by a subscription ID or parameter value.  

    For example, you can pull Product ID and name, Vendor ID and name, Marketplace ID and name, as well as other data by passing the subscription ID or parameter value to the Connect API. 

    Lookup CloudBlue product item 

    This transformation function allows you to get an extended CloudBlue product item data by a product item ID or MPN. 


    If there is no product linked to your stream, you need to indicate Product ID manually or specify the column from which Product ID must be taken.

    Lookup CloudBlue fulfillment request data

    This transformation function allows you to get the CloudBlue fulfillment request data by the parameter value as a mandatory search criterion and subscription id as an optional one:

    Lookup CloudBlue billing request data

    This transformation function allows you to get the CloudBlue billing request data by the subscription ID or parameter value:

    Get standard VAT rate for EU country 

    This transformation function is performed using the latest rates from the LINK API. The input value must be either a two-letter country code defined in the ISO 3166-1 alpha-2 standard or country name. For example, ES or Spain. 


    This transformation function allows you to perform mathematical and logical operations on columns and context variables using the jq programming language. 

    You can add several formulas to one transformation rule. For example, first, you may need to get price multiplied by 12 and then price multiplied by 13 – you can make this calculation using two different transformations or add both operations to one transformation.


    When adding formula-based transformations, you can see the list of suggested columns by typing “.” and the list of context variables by typing the “$” sign.

    Delete rows by condition

    This transformation function allows you to delete rows that contain or do not contain a specific value or values. Rows can be filtered and removed by a value match or mismatch.

    Convert currency 

    This transformation function allows you to convert currency rates using the API

    Copy columns 

    This transformation function allows you to copy input column values to an output column. You can use it to rename a column, for example, or create a copy of column values for another purpose.  

    Lookup data from Excel file attached to stream 

    This transformation function allows you to populate data from the attached Excel file by matching column values between input and attached tables. 

    Lookup data from AirTable 

    This transformation function allows you to populate data from AirTable by matching input column values with AirTable table values. 

    You can store data in a central place using the AirTable cloud service and share this data between multiple streams and Marketplaces. 

    To add this transformation rule, you need to upload your Excel file to AirTable, create a token in Personal access tokens of the Developers hub, and when creating a new row-level transformation, choose Lookup data from AirTable in the drop-down list: 

    You then need to insert the copied token into API key and map columns and sheets between your stream Excel file and the Airtable one.

    Defining Transformation Rules

    Transformation rules can be applied to your data streams in order to define the expected output for associated batches.

    The system allows you to define transformation rules for your streams in the following way:

    Access the Transformation tab from the corresponding Stream Details screen. Click the Add button to create a new transformation rule.

    Next, select transformation type by using the corresponding field in the drop-down list, fill in all mandatary fields, and click Save. You can add more transformation rules by clicking on the Add button above your rule on the right side.

    Once your transformation rules are defined successfully, continue with your stream configuration or publication as described in the Commerce User Interface article.

    Transformation Requests

    You can update transformation data for your batch using transformation requests. Go to the Commerce module and choose Requests in the left navigation menu, then click the link of the transformation request you need to update. In Requests, you can perform the following operations:

    • Upload the required output data for your transformation by using the corresponding Upload Output button. The system allows previewing both input and output data.
    • Switch the assignee for the transformation operation by clicking the pencil icon and changing the assignee of the request.
    • Abort your transformation request, using the arrow () icon at the top-right corner of the screen and clicking Fail request.

    Filling out all transformation data is required to successfully complete and publish your batches. Continue with your batch configuration or publication as described in the Commerce User Interface article.

    Batch Details Transformation

    You can also work with transformation rules by accessing the Transformation requests tab of a selected batch:

    This provides interface that is similar to the transformation data management introduced within the transformation requests.

    Is this page helpful?
    Translate with Google
    Copied to clipboard