Contextures

Use Power Query to Combine Tables

You can use Power Query (Get & Transform Data) to combine the data from 2 or more tables, if those tables have some column headings with identical names. Then, create a pivot table from the combined data

NOTE: In older versions of Excel, use the Multiple Consolidation feature

Pivot Table from Data on 2 Sheets

To create a pivot table from table data on two or more sheets, use Excel's Power Query (also called Get & Transform Data). With a few simple steps, you can combine all the data, as long as those tables have some column headings with identical names.

Watch this video see the steps, and the written instructions are below the video.

Data in Two Tables

To combine the data with this technique, at least one column heading must be an exact match in the two tables.

In this example, the Excel workbook has data from two regions - East and West. The data is in two separate tables. The two tables are not identical, but most column headings have an exact match in the other table.

  • In the East data (blue), there is a Manager column that is not found in the other table
  • In the West data (black), there is a Tax column that is not found in the other table.

different table structures

Add the Tables

The first step is to create a query for each table, to get its data.

If you're using Power Query:

  1. Select a cell in the East table.
  2. On the Ribbon, click the Power Query tab, then click From Table get data from Excel Table

If you're NOT using Power Query:

  1. Select a cell in the East table.
  2. On the Ribbon, click the Data tab, then click From Table/Range get data from Excel Table

     

  1. In the Power Query window that opens, click Close & Load

    get data from Excel Table

  2. A new sheet is inserted, with the data from the East table. At the right, you can see a list of queries, with a Refresh button at the right side of each query.

    get data from Excel Table

Next, repeat those steps, to get the West table data.

  • get data from Excel Table

Combine the Table Data

Next, you will create a another query to combine the data from the two tables.

If you're using Power Query:

  1. Click the Power Query tab on the Ribbon
  2. Click the Append command, in the Combine group.
  3. get data from Excel Table

If you're NOT using Power Query:

  1. Click the Data tab on the Ribbon
  2. Click the Get Data arrow, then click Combine Queries, and click Append.
  3. get data from Excel Table

  • Then, in the Append window, select a table from each drop down, then click OK.
  • get data from Excel Table

A window opens, showing the combined data. For columns that are unique to one of the tables, rows from the other table will show null, such as the Tax and Manager columns in the screen shot below.

  • Click Close & Load.
  • get data from Excel Table

A new sheet appears, with the combined data.

get data from Excel Table

Create a Pivot Table

Next, you can use the combined data to create a pivot table

  1. Select a cell in the combined data, and on the Ribbon's Insert tab, click Pivot Table

    get data from Excel Table

  2. Use the PivotTable Field List to add fields to the pivot table, to see a summary of the data in the two original tables.

    get data from Excel Table

Combine 3 Excel Tables

To see the steps for combining 3 tables with Power Query, watch this short video tutorial by Mike "ExcelIsFun" Girvin. If you have more than 3 tables, use the same technique to add the extra tables.

Download the Sample File

Download the sample Combine Tables With Power Query file

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Get weekly Excel tips from Debra

More Pivot Table Resources

Pivot Table Blog

Pivot Table Article Index

Add-In: PivotPower Premium

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Last updated: March 4, 2021 7:20 PM
Contextures RSS Feed