Excel: Merging Data with Power Query (Bonus Video!)

Power Query in Excel is an incredibly powerful tool. A while back, I posted a brief introduction to this feature. I wanted to expound upon that information with another great use for Power Query: merging data. This is a common situation: a lot of us have data files we are pulling from different sources that contain unique information. Combining this data into one powerhouse sheet can be a great time saver. Let’s take a look at how to do this.

Power query screen

Video

Hey, I am trying something different! Thank you to those of you who completed our ITS Applications Training Survey last week. A ton of you said you wanted videos and exercises, so let’s try it out today. Below is a link to a YouTube video, and read on for the exercise files. I will also include written instructions, as per usual.

 

Exercise Files

Here are a couple downloads for you:

MergeExercise:  Start Here

MergeExerciseSolved: to check your work

Two Disclaimers

  • For this Byte, I am assuming you already have some basic knowledge about Excel, like Tables and PivotTables. If you would like to learn more about either of these, I would love for you to attend a session! Look for sessions in myTraining.
  • Also, you will likely need to be using the Windows version of Excel, preferably 365, though this should also be possible in 2016.

About This Workbook

This spreadsheet contains three sheets: each with unique information about our key players in the name column.

  • The first sheet contains their number and location,
  • The second contains their favorite animal, and
  • The third their favorite candy. Our goal is to bring all this data together in one powerhouse worksheet.

Date

Import Data as Queries

1. In the first sheet, place your cursor in the middle of the data. Press Ctrl + T to turn it into a table. You can also do this from the dropdown in the Home tab.

2. Make sure “My table has headers” is checked and all the data is selected, and press OK. Repeat this step with each sheet of data, so you have a total of 3 tables in the worksheet.

table setting

3. Select one of the tables. Go to the Data tab in the ribbon, Get & Transform data group, and select From Table/Range. If you don’t see this button in the Get & Transform Data group, check the Get Data dropdown for it.

Data tab

…you will be taken to the Power Query editor screen…

Power query screen4. In Applied Steps on the right, click the X next to changed type to remove it.

x next to changed type

5. In the upper left select the dropdown for Close & Load To…

close and load to

6. Select Only Create Connection and press OK.

only create connection

7. Repeat Steps 3 – 6 for all the tables you created. You will see a list of tables appear on the right side of the screen under Queries and Connections.

Queries and connections

Merge the First Two Queries

1.  In the Data tab, go to the Get Data dropdown, hover your mouse over Combine Queries, and select Merge.

Combine data, merge

2. In the Merge screen, select your first table in the upper dropdown, and your second table in the second dropdown.

select tables

3. Select the two common columns: in this case, it is the Name column. Click on the Name column under each table. Press OK.

Select name column

4. In the Power Query screen, find the double headed arrow at the top of the last column, from the merged table. Click on this button to expand information.

double headed arrow to expand

5. Select what you would like to expand. In this case, we are unchecking the name box, and just keeping Animal selected. Press OK.

select animal only

This will expand the Animal column from the second table. Note that it applies its own naming format to the column title. You can rename the column, if desired. Double click on the title to change the name.

CHange name of column

Keep this Power Query screen open for the next step.

Merging Subsequent Queries

We now have 2 merged queries. We can add the last one from within the Power Query screen.

  1. In the Power Query ribbon, Combine group, select Merge Queries.

Merge

2. In the Merge screen, your Merged query will appear at the top by default. In the second dropdown, select the final table. Just like you did in the previous section, select the Name columns in both queries, and press OK.Merge

3. Just as you did in the previous section, the final table appears at the top with a double headed arrow. Click on the double headed arrow to expand. And leave only Candy selected. Press OK.

only candy selected

4. Select Close and Load To… but this time keep the default selected: Table on a New Worksheet. Press OK.

Close & Load Screen

Final Table

Our merged query is visible as a table on a new worksheet.

Final Table

A Couple Notes

It helps if you think about your final table like a Pivot Table:

  • If you need to change something about the data, be sure to change the data source (one of the data sheets), not the merged query.
  • When you do change the data source, be sure to refresh the query. A simple way to do this is to right click on the merged table and select Refresh.

Right click refresh

Thoughts?

What do you think? Did you like the video? I hope this was helpful. Feel free to send me an email anytime.

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • La’Rell Marion (picture coming soon!)