Contextures

Use Power Query to Combine Tables

Use Power Query to combine data from different sheets in a workbook, or from different Excel files, if those tables have some column headings with identical names. Then, you can create a pivot table from the combined data

Create a Pivot Table from Data on Different Sheets

In older versions of Excel, you could use the Multiple Consolidation feature, to create a pivot table from data on different sheets.

Use Power Query to combine data from different sheets in a workbook, or from different Excel files, if those tables have some column headings with identical names.

different table structures

With the combined data, you will be able to create a normal pivot table, showing the data from both original tables.

pivot table from combined tables

Add Tables With Power Query

  1. Select a table, including the headings.
  2. On the Ribbon, click the Power Query tab
  3. Click From Table, in the Excel Data group.

    get data from Excel Table

  4. In the window that opens, click Close & Load

    get data from Excel Table

  5. A new sheet is inserted, with the data from the first 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

  6. Repeat the steps to add the remaining tables..

    get data from Excel Table

power query training

Combine the Table Data

Next, you will use Power Query to combine the data from the tables.

  1. Click the Power Query tab on the Ribbon, and click the Append command, in the Combine group.

    get data from Excel Table

  2. Select a table from each drop down, then click OK.

    get data from Excel Table

  3. 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.
  4. Click Close & Load.

    get data from Excel Table

A new sheet appears, with the combined data.

get data from Excel Table

 

power query training

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

Video: Combine Excel Tables With Power Query

To see the steps for combining tables with Power Query, and creating a pivot table from the combined data, watch this short video tutorial.

Video: Combine 3 Excel Tables With Power Query

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

power query training

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter.

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

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: September 13, 2016 7:15 PM
Contextures RSS Feed