Читать книгу Excel Power Pivot & Power Query For Dummies - Michael Alexander - Страница 27

The first step in using Power Pivot is to fill it with data. You can either import data from external data sources or link to Excel tables in your current workbook. I cover importing data from external data sources in Chapter 4. For now, let me start this walkthrough by showing you how to link three Excel tables to Power Pivot.

You can find the sample file for this chapter on this book’s companion website at www.dummies.com/go/excelpowerpivotpowerqueryfd2e in the workbook named Chapter 2 Samples.xlsx.

In this scenario, you have three data sets in three different worksheets: Customers, InvoiceHeader, and InvoiceDetails (see Figure 2-2).

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.

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