How to Combine Multiple Data Sets in Microsoft Excel Using Power Query

Microsoft Power Query is a useful tool to work with data inside of Microsoft Excel. It comes with a lot of features that make managing data sets simple yet powerful.

Power Query is most useful when working with multiple sets of data, rather than just one. It acts as a bridge between sets of data and Microsoft Excel.

To prove it, we’ll walk you through a common task where Power Query shines: combining two different data sets into one.

What Is Microsoft Power Query?

Microsoft Power Query is a tool included in Microsoft Excel for Windows. It’s only included in 2016 or newer versions, so check those versions to begin using. New users should make sure they’re pretty comfortable in Excel before starting to use Power Query.

What Power Query allows you to do is load data from a number of different sources, edit that data, and then import it into an Excel worksheet. Learn more about why you should start using Microsoft Power Query.

One of the source types from which you can pull data is other Excel sheets. To get the data for this Power Query demonstration, we are going to use two different Excel worksheets containing similar data sets, and then combine them into one.

Setting Up Your Data

Let’s take two Excel workbooks, one titled “Cars.xlsx” and one titled “Trucks.xlsx”. The contents of these sheets are simple. Each sheet contains a table describing certain vehicles categorized by columns: “Make”, “Model”, “Color”, and “Year”.

We are also creating a blank workbook titled “Vehicles.xlsx” that we will do our work in.

To keep things simple we are using two data sets with the same header information. Our objective here is to create a new table with both car and truck information.

Now that these workbooks are saved to the computer, let’s get to work with Power Query!

Loading Data Into the Power Query Editor

To begin, all you need to do is open the workbook you want to contain the finished data. In this case, the workbook is “Vehicles.xlsx”.

Power Query is so easy, you don’t even have to open the workbooks containing data to extract what you need. In “Vehicles.xlsx” navigate to the “Data” tab of your workbook options.

You will see the “Get Data” option in your toolbar. This option is the entry to Power Query and will allow you to choose your source of data.

You want to work with an Excel workbook, so choose the “From File” options followed by “From Workbook”.

”

The editor will open a file explorer and you can navigate to a workbook anywhere on your PC. You are going to choose the “Cars.xlsx” file first.

Once you select your file, the Power Query Navigator menu will load to show you a preview of the sheet you selected. Notice the file “Cars.xlsx” as well as the sheet is displayed in the Navigator. Click on the sheet and the table will load on the menu just as it appears in your worksheet! Click on “Transform Data” to open the data in the editor.

Using the Power Query Editor

The Power Query editor is packed with a lot of options, don’t worry about those right now while you get familiar with the basics. At first glance, the data has been uploaded properly! It looks just like an Excel table. Data is organized in rows and columns, with named headers at the top.

On your right is a menu called “Query Settings” which contains an “Applied Steps” window. Steps are just changes made to your table. Keep an eye on this for later.

On the far left clicking on the “Queries” icon will expand the menu and show the name of the table you are currently working in. Power Query can take multiple tables at one time and will display them all in this toolbar.

In order to append two tables together, they both need to be uploaded into Power Query. You have one, let’s grab the other.

Adding a Second Data Source

Inside of the editor, at the top right corner, is the “New Source” button. This button allows you to select an additional data source and add it to the Power Query Editor.

Choose “File” and then “Excel” to open the file explorer. Navigate to “Trucks.xlsx” to select the file for importing.

Just like the first Query, the Navigator will pop up allowing you to choose your table from the worksheet. Select the worksheet click “OK”. You will now see both the tables from the cars and trucks worksheets in the Queries toolbar. Clicking on either table will display the data contained in that worksheet.

Appending the Data Tables

Once you’ve done all the preparation, combining the data tables is actually quite simple. Click onto the “cars” table, and in the toolbar select “Append Queries” within the “Combine” section.

The Append window will pop up and prompt for a table that you wish to join to your selected table. Remember that you are on the “cars” table and appending the “trucks” table to it. Click on the trucks table under “Table to append” and choose “OK”

Here is the result:

Notice that all the car data and truck data are together on the same table. The data sets are now joined! Also notice the Power Query has created a new “step” in your list, an “Appended Query” step.

Loading the Data to Your Worksheet

The last thing to do is take this data stored on the Power Query editor and load it onto your worksheet.

In the top left corner click on “Close & Load” to place the newly created table onto the Vehicles sheet in table format. Upon clicking, the following will happen:

• The editor will save your Query steps and exit
• The Workbook will appear with the data loaded as an Excel Table
• Excel will also open the Workbook Queries menu on the right side of your sheet

You’re ready to work with your brand new table, all without ever opening up the worksheets.

More Things You Can Do With Microsoft Excel

Now that you’ve learned how to append data using Power Query you have a powerful new tool to simplify data. Now that data is loaded into Excel to use you can work with it just like any table. You can create graphs and charts from Excel tables or summarize data using Pivot Tables.

Read the full article: How to Combine Multiple Data Sets in Microsoft Excel Using Power Query

Read Full Article