Excel Power Pivot & Power Query For Dummies
Book image
Explore Book Buy On Amazon
The first step in using Power Pivot is to fill it with data. One way is to link Excel tables to Power Pivot. In this scenario, you have three data sets in three different worksheets: Customers, InvoiceHeader, and InvoiceDetails.

You want to use Power Pivot to analyze the data in the Customers, InvoiceHeader, and InvoiceDetails worksheets.

You can find the sample files for this exercise on in the workbook named Chapter 2 Samples.xlsx.

The Customers data set contains basic information, such as CustomerID, Customer Name, and Address. The InvoiceHeader data set contains data that points specific invoices to specific customers. The InvoiceDetails data set contains the specifics of each invoice.

To analyze revenue by customer and month, it's clear that you first need to somehow join these three tables together. In the past, you would have to go through a series of gyrations involving VLOOKUP or other clever formulas. But with Power Pivot, you can build these relationships in just a few clicks.

When linking Excel data to Power Pivot, best practice is to first convert the Excel data to explicitly named tables. Although not technically necessary, giving tables friendly names helps track and manage your data in the Power Pivot data model. If you don't convert your data to tables first, Excel does it for you and gives your tables useless names like Table1, Table2, and so on.

Follow these steps to convert each data set into an Excel table:

  1. Go to the Customers tab and click anywhere inside the data range.
  2. Press Ctrl+T on the keyboard. This step opens the Create Table dialog box, shown here.

    Convert the data range into an Excel table.
  3. In the Create Table dialog box, ensure that the range for the table is correct and that the My Table Has Headers check box is selected. Click the OK button. You should now see the Table Tools Design tab on the Ribbon.
  4. Click the Table Tools Design tab, and use the Table Name input to give your table a friendly name, as shown. This step ensures that you can recognize the table when adding it to the Internal Data Model.

    Give your newly created Excel table a friendly name.
  5. Repeat Steps 1 through 4 for the Invoice Header and Invoice Details data sets.

About This Article

This article is from the book:

About the book author:

Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.

This article can be found in the category: