How To Combine 2 Data Sets In Excel

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 information sets simple yet powerful.

Ability Query is most useful when working with multiple sets of data, rather than only one. Information technology acts every bit a span between sets of data and Microsoft Excel.

To bear witness it, we'll walk y'all through a common chore where Power Query shines: combining two different information sets into 1.

What Is Microsoft Power Query?

Microsoft Ability Query is a tool included in Microsoft Excel for Windows. Information technology'southward just included in 2022 or newer versions, so check those versions to brainstorm using. New users should make sure they're pretty comfy in Excel before starting to utilize Ability Query.

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

One of the source types from which y'all tin pull data is other Excel sheets. To get the data for this Ability Query sit-in, nosotros are going to use ii unlike Excel worksheets containing similar data sets, so combine them into one.

Setting Upwardly Your Data

Permit's take ii Excel workbooks, ane titled "Cars.xlsx" and ane titled "Trucks.xlsx". The contents of these sheets are elementary. Each sail contains a table describing sure vehicles categorized by columns: "Brand", "Model", "Color", and "Yr".

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

To keep things simple we are using ii data sets with the aforementioned 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 figurer, let'southward go to work with Power Query!

Loading Information Into the Power Query Editor

To begin, all you need to do is open the workbook you desire to comprise the finished data. In this case, the workbook is "Vehicles.xlsx".

Ability Query is then like shooting fish in a barrel, you don't even have to open up the workbooks containing information to extract what you demand. In "Vehicles.xlsx" navigate to the "Information" tab of your workbook options.

You will see the "Get Information" option in your toolbar. This option is the entry to Power Query and will let you to choose your source of data.

Yous desire to piece of work with an Excel workbook, so cull the "From File" options followed by "From Workbook".

Importing Data From Excel Into Power Query

The editor volition open up a file explorer and you can navigate to a workbook anywhere on your PC. You are going to choose the "Cars.xlsx" file offset.

In one case you lot select your file, the Power Query Navigator menu volition load to show you a preview of the sheet yous selected. Detect 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 merely as information technology appears in your worksheet! Click on "Transform Data" to open up the data in the editor.

Using the Ability Query Editor

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

On your right is a carte du jour called "Query Settings" which contains an "Applied Steps" window. Steps are just changes fabricated 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 tin can accept multiple tables at one time and will brandish them all in this toolbar.

In social club to append two tables together, they both need to exist uploaded into Power Query. You take ane, let's take hold of the other.

Calculation a Second Data Source

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

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

But similar the start Query, the Navigator will pop upwardly allowing you to choose your table from the worksheet. Select the worksheet click "OK". You will at present 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.

Displaying Power Query Menu for Cars and Trucks

Appending the Data Tables

Once yous've done all the grooming, combining the information tables is actually quite simple. Click onto the "cars" table, and in the toolbar select "Suspend Queries" within the "Combine" department.

The Append window will popular up and prompt for a tabular array that you wish to bring together to your selected table. Remember that yous are on the "cars" table and appending the "trucks" table to it. Click on the trucks table under "Table to append" and choose "OK"

Appending Data Sets in Power Query for Excel

Here is the result:

Notice that all the auto data and truck data are together on the same table. The information sets are at present joined! Also detect the Power Query has created a new "step" in your listing, an "Appended Query" pace.

Loading the Data to Your Worksheet

The last thing to do is take this data stored on the Ability 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 tabular array format. Upon clicking, the post-obit will happen:

The editor will save your Query steps and get out
The Workbook will appear with the information loaded as an Excel Table
Excel volition as well open the Workbook Queries carte du jour on the correct side of your sheet

Completed Excel Table from Power Query Upload

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

More Things Y'all Can Do With Microsoft Excel

Now that you've learned how to append data using Ability Query you lot accept a powerful new tool to simplify information. Now that data is loaded into Excel to use you tin piece of work with information technology simply like any table. Yous can create graphs and charts from Excel tables or summarize data using Pivot Tables.

The Best Hidden Cameras for Protecting Your Home