Use Lookups to Transform Values

How to use Lookups into an external data source to Transform Values for synchronisation

Step 1: Connect to a source in this case a simple list of numbers

Our source data contains the numbers 1-10 and we want to convert these into the names of the Numbers.

Source Data

We need then to create a Transformation Data source and for this we're going to use Excel. We could use virtually anything we like, SQL, CRM, SharePoint, XML, CSV etc but Excel is ideal for this.

Step 2: Prepare the lookup Values (for this example)

This Excel file contains the From and To values for the Transformation, we need to create Column headers in the First row and then the Data below i.e.

Lookup Values

Step 3: Add a Lookup Column

Now we add the Lookup to the column (select the ID column, and press the 'Add Lookup' toolbar button) that we want to Transform, in our Example this is the ID column. First we select the Data source which for this example is Excel and browse to the Path of the Excel file.

Add Lookup

Step 4: Set the lookup column details

Now we define the Lookup connection details, so we want to join the Lookup based on the Value in the From Column to the Value in the ID column in our source data. We also specify the Data Type as Int32 since this is a Number 1-10.

Lookup Connection

Step 5: Use the values from the lookup table

We then add the 'Lookup1_To' column from the Data source to our Schema Map so that we return the Value of the Lookup Transformation.

Schema Map

There we have it a Transformation from an External Data source via Lookups.

Preview

This simple Transformation could be created in code via Dynamic Columns but this shows a no-code solution and has the added benefit that you can easily extend the transformation values by updating the Excel data source.


Can't find the answers you're looking for? Contact our support team and we'll personally get back to you as quickly as we can.