Merging/Joining datasets in Excel using Data Explorer add-in

Standard

Problem:

Merging/Joining/Combining data-sets in Excel has not been an easy task. There are third-party add-ins that makes it easy but out of the box, excel didn’t have an easy way to merge/join table data. But now with the Data Explorer add-in, we have an add-in that let’s us merge/join data in excel w/ few clicks.

If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx 

Situation:

Input is Table 1 & Table 2. The output we need is merged Table.

Table 1:

DateDaily New numberMonth
1/1/201201
1/2/201201
1/3/201201
1/4/201201
1/5/201201
1/6/201201
1/7/201201
1/8/201201
1/9/201201

………………………………..

Table 2:

MonthMonth Name
1January
2February
3March
4April
5May
6June
7July

Merged Table:

DateDaily New numberMonthMonth Name
1/1/201201January
1/2/201201January
1/3/201201January
1/4/201201January
1/5/201201January
1/6/201201January
1/7/201201January
1/8/201201January
1/9/201201January

………………………………

Solution:

Let’s see how data explorer can help us Join/Merge Table 1 & Table 2.

1) create query that connects to Table 1 & Table 2.

data sources explorer excel

2) Once you have queries that connect to the tables need to be merged, then click on Merge

3) Once you click on Merge, you’ll see a dialog:

Here you need to configure three things:

a) First Table

b) Second Table

c) Columns that will be used to merge/join data

In this case, this is how my merge dialog looks:

merge join excel data explorer

4) Once configured correctly, click on OK. You’ll see a dialog box where you can configure the output of the merged table. click on the new column to see the options that are available to you to configure the output of the merged table:

merge join excel data explorer 2

5) In this case, I’ve selected just one column month name that needs to be merged. You can also explore the aggregate tab in case you’ve numbers that needs merging.

merge join excel table data explorer 3

6) This is how the output looks:

merge join excel table data explorer 4

7) Rename the new column.

Select the new column > Right Click > Rename

8) Click Done if it looks OK.

9) The merged data is now available to you in Excel!

And one can analyze it!

Let’s see before and after. Note that instead of month numbers, we now have month names

merged data join table visualized excel 3

In this post, we saw how to merge/join/combine data from two different sources in Excel 2010.

0 thoughts on “Merging/Joining datasets in Excel using Data Explorer add-in

What do you think? Leave a comment below.