How to combine multiple data sets in Microsoft Excel with Power Query

Microsoft Power Query is a useful tool for working with data inside Microsoft Excel. It comes with lots of features that help manage simple but powerful datasets.

Power Query is most useful when working with multiple data sets. It acts as a bridge between data sets and Microsoft Excel.

To demonstrate that, the article will guide you to perform a common task where Power Query shines: Combine two different data sets into one.

What is Microsoft Power Query?

Microsoft Power Query is a tool included in Microsoft Excel for Windows. It is only included in versions of Excel 2016 or newer, so check these versions to start using Power Query. New users should make sure they understand the basics of Excel before starting to use Power Query.

What Power Query allows you to do is load data from several different sources, edit that data, and then import the data into an Excel spreadsheet. Learn more about why you should start using Microsoft Power Query.

One of the types of sources that you can get data from is other Excel worksheets. To get the data illustrated for Power Query in this article, the example will use two separate Excel spreadsheets, containing the same data sets, then combine them into one.

Data set

Choose 2 Excel workbooks, respectively 'Cars.xlsx' and 'Trucks.xlsx' in the example. The content of these spreadsheets is very simple. Each worksheet contains a table describing certain media classified by the columns: 'Make', 'Model', 'Color' and 'Year' .

The example is also creating an empty workbook titled 'Vehicles.xlsx' to do its job.

For simplicity, the article is using two data sets with the same header information. The goal here is to create a new board with both car and truck information.

These workbooks have been saved to the computer. Now let's work on Power Query!

Load the data into the Power Query Editor

To get started, all you need to do is open the workbook containing the created data. In this case, the workbook is 'Vehicles.xlsx'.

Power Query is very easy to use. You do not need to open a workbook containing data to extract what you need. In 'Vehicles.xlsx', navigate to the Data tab containing options for the workbook.

You will see the 'Get Data' option in the toolbars. This option is an entry to Power Query and will allow you to select your data source.

You want to work with an Excel workbook, so select the 'From File' option , followed by 'From Workbook' .

The editor will open the file explorer and you can navigate to the workbook anywhere on the PC. First, you will select the file 'Cars.xlsx'.

When you have finished selecting your file, the Power Query Navigator menu will load to show you a preview of the selected worksheet. Note the 'Cars.xlsx' file , as well as the worksheet displayed in the Navigator. Click on the sheet and the worksheet will load on the same menu as it appears in the worksheet! Click 'Transform Data' to open the data in the editor.

Use the Power Query Editor

Power Query Editor has a lot of options, don't worry about those right now, as you will only get acquainted with the basics of this article. At first glance, the data has been uploaded properly! It looks like an Excel table. The data is organized in rows and columns, with headings named at the top.

To your right is a menu called 'Query Settings' that contains an 'Applied Steps' window. The steps here are changes made to the table. Note this section for later use.

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

To join the two tables, both tables need to be uploaded to Power Query. You already have one, so upload the other one.

Add a second data source

Inside the editor, in 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.

Select 'File' and then 'Excel' to open the file explorer. Navigate to 'Trucks.xlsx' to select the file to import.

Like the first query, the Navigator will appear, allowing you to select your table from the spreadsheet. Select the spreadsheet, then click OK. Now you will see both car and truck boards in the Queries toolbar . Clicking on one of the two tables will display the data in that spreadsheet.

Join the data sheet

Once all the steps have been prepared, combining the data tables is actually quite simple. Click on the 'Cars' panel , and on the toolbar, select 'Append Queries' in the 'Combine' section.

The Append window will appear and mention the table you want to merge into the selected table. Remember that you are on the 'Cars' board and want to associate the 'Trucks' board with it. Click the 'Trucks' table inside the 'Table to append' to combine and select OK.

Here is the result:

Note that all car and truck data will be placed in the same table. The datasets have been combined! Also, note that Power Query has created a new 'step' in your list, called 'Appended Query'.

Load data into the worksheet

The last thing to do is to take the data stored in the Power Query Editor and load it into your spreadsheet.

In the top left corner, click 'Close & Load' to place the newly created table into Vehicles in table format. When clicked, the following will happen:

1. The editor will save the query and exit steps.
2. The workbook will appear with the data loaded as an Excel table.
3. Excel will also open the Workbook Queries menu on the right side of the worksheet.

You are ready to work with brand new spreadsheets without opening a spreadsheet!

You have now learned how to append data with Power Query. You have a powerful new tool to simplify data. Now that the data has been loaded into Excel for use, you can work with it like any table. You can create charts from Excel tables or insert pictures in bulk into Excel cells.

Hope you are succesful.