Combining data from multiple sources

1. Steps to combine data from multiple sources
2. Adding multiple sources
3. How to remove sources from the importer?
4. Adding multiple transformations

Coupler.io allows you to consolidate and merge data from different sources into a unified dataset. You can fetch data from one or more data sources and combine it in the destination - all with a single importer.

Combining data from two or more sources is available in two modes in Coupler.io: JOIN and APPEND.

Steps to combine data from multiple sources

1. Add the needed sources to the importer. Set up their parameters.
2. Proceed to the "Transformations" step. 
3. Preview and transform the data in every source based on your needs. 
4. Select the mode in which you want to combine data: JOIN or APPEND.
5. Preview and transform the combined data based on your needs. 
6. Proceed to the "Destinations" step. Set up its parameters.
7. Set the needed schedule for your importer, and run it.

Adding multiple sources

1. When setting up an importer in Coupler.io, the first step is to set up your Data source to extract data. After setting up your first source, you will have the option to add a new source or duplicate the existing one.

Adding a new data source:

Duplicating a data source (this action will copy all the settings from your current source, so you can modify only the needed ones):

2. Add as many data sources as you need. You may also edit the importer name for better identification:

3. Move on to the next step - Transformations: 

Coupler.io will take a moment to pull data from your selected sources. Once the data is pulled, it will be loaded and shown under Transformations:

Once data is loaded on Transformations, you may:

• Switch between sources on the left panel to preview what data was returned for each source
• Preview and transform the data in each separate source based on your needs

See more tips on Data transformation

Then, you can select the type of data combining you need - Append or Join:

These two Data Combining modes are described here:

• Data Append
• Data Join

How to remove sources from the importer?

To remove completely:

• Go to the "Data Sources" step on your importer, and select "edit" near the source you want to remove:

• Click on the bin icon and confirm deletion:

  

To disable a source in the importer without deleting it completely:

• Go to the "Data Sources" step in your importer, and switch the toggle off near the source you want to disable

  

This action preserves the source's settings but removes data from this source on Transformations (and it won't be imported to the destination accordingly). If you enable the source back on, the data will appear on Transformations again. 

Adding multiple transformations

Coupler.io allows adding several layers of transformations to your dataset. This functionality allows you to:

1. Add as many sources to your importer as you need, then choose to combine them independently in Append/Join. Eg: you may add 4 Sources to your importer, then >> 

• Append Source 1 with Source 2 (Transformation #1);
• Join Source 3 with Source 4 (Transformation #2);
• on the "Destinations" step, you may choose which specific dataset to import to your destination. It can be Transformation #1, or Transformation #2

2. Add as many sources to your importer as you need, combine them in Append/Join, then use these Append/Join datasets as sources for your next transformations. Eg:

• Append Source 1 with Source 2 (Transformation #1);
• Join Source 3 with Source 4 (Transformation #2);
• Append Transformation #1 with Transformation #2 (Transformation #3)
• Add Source 5
• Join Transformation #3 with Source 5
• and so on

See it in action step-by-step

1. Add the needed sources to your account. You can give each source a unique name for better navigation:

- Data sources section >> edit a source:

- Right in Transformations:

2. Add your first transformation by selecting the data combining mode from the preview, or by hitting "+ Add transformation" option from the left menu:

How data combining modes work:
- Data Append
- Data Join

Now that you've added the transformation, it appears as a separate dataset view on the left menu. Select it from the menu to preview the results of the combined data. Same as with sources, you can give your transformation view a unique name: 

3. To add another transformation, pick the next source where you want to combine data >> pick Append/Join on preview, or choose "+ Add transformation" from the left menu:

3. Add as many transformations as needed to your importer:

To edit/ delete the specific Join/Append view, open its preview and navigate here: 

4. Apply any necessary additional transformations to your Append/Join views or to your Source views. The most common scenario is to align your original column names in Sources used for Append views; hide/ re-order columns in Join views for making neatly looking combined datasets. 

See more tips on  Data transformation

5. After adding all the needed transformations, proceed to the "Destinations" step. Choose the data view for import into the destination, and set up the remaining destination parameters.

6. Set the needed schedule for your importer, and run it.

A use-case example 

We want to combine Xero Invoices and Credit notes in one data set. We also want to show an account associated with invoices and credit notes. On top of that, we want to repeat the same for our 2nd Xero tenant and combine data from 2 tenants together.

Steps:

1. Add a Xero importer, connect the source account, and pick tenant #1 in the source settings

2. Pick Invoices as data entity for import, and set other needed parameters of the source. Rename the source, and duplicate it:

3. In the duplicated source, change the data entity to Credit Notes. Add another source for Accounts with the same steps, and proceed to the Tranformations:

4. Add the first transformation: Append Invoices and Credit Notes. Rename the received Append view accordingly:

5. The Append matched data as-is from the sources. Since we want an aligned dataset, let's modify columns in our sources accordingly:

1. hide unneeded columns
2. rename columns to match in both sources ( e.g. column "CreditNoteID" in Credit Notes source, and "InvoiceID" in Invoices source we rename to just "ID" in both sources, so we get aligned data under the same column in our Append view)
3. preview Append view to confirm it looks as we want

6. Now we need to add the Account Name to Invoices & Credit Notes (the original data in these sources has only Account id). Add the second transformation: Join "Accounts" Source to "Invoices & Credit Notes" View:

hit "+Add transformation" in the left panel >> choose "Join" >> join our existing "Invoices and Credit Notes" View with "AccountID" source by the key columns "LineItems.AccountID" and "AccountID" accordingly >> press "Join data":

7. Preview the results, give the received Join view the proper name:

 7.1. Hide unnecessary columns received after left-joining the Accounts data: we hid "LineItems.AccountID" and all extra columns from "Accounts":

8. We are all set with combining Invoices, Credit Notes and adding Account name to them for our 1st tenant 🎉 Proceed to the Destinations step, set up your needed destination, save and run the importer.

Now to add the same information from the 2nd tenant to the same importer, we can go 2 ways: 

Option 1:

Add 3 more sources to your importer: Invoices, Credit Notes, and Accounts only this time change the tenant in source settings. Append and Join information in them using the same steps as above.

At the end, Append "Invoices with Credit Notes and Account" from tenant 1 view to  "Invoices with Credit Notes and Account"  from tenant 2 view.

Option 2: 

1. Copy your importer (instead of adding new sources) 

2. When copied, edit each source by changing your tenant in source settings:

Change it for all 3 sources.

3. Proceed to Transformations. You will see the same transformations copied from the previous importer, including your ready "Invoices with Credit Notes and Account" view. Only this time, it will contain data from tenant #2. 

4. Add one more source to your importer: 

Select Coupler.io as your source, and choose the previous importer: 

5. Proceed to Tranformations. 

Add another transformation >> Append >> choose to combine "Invoices with Credit Notes and Account" and Coupler.io source:

6. Now all of your data is combined from both tenants. To easily differentiate which tenant data comes from, do this trick: add a formula column to Coupelr.io source and to "Invoices with Credit Notes and Account" View:

Now you have all data combined and you see which tenant it comes from:

7.  Proceed to the Destinations step, set up your needed destination.

8. Set the needed automatic schedule for your importer. 

9. Save and run the importer.

If you have any questions, please write to our Support team!