Excel Power Pivot & Power Query For Dummies. Michael Alexander
alt="Ontheweb"/> 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.
Preparing Excel tables
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 in Figure 2-3.FIGURE 2-3: 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 Design tab on the Ribbon.
4 Click the Table Design tab, and use the Table Name input to give your table a friendly name, as shown in Figure 2-4.This step ensures that you can recognize the table when adding it to the Internal Data Model.
5 Repeat Steps 1 through 4 for the Invoice Header and Invoice Details data sets.
FIGURE 2-4: Give your newly created Excel table a friendly name.
Adding Excel Tables to the data model
After you convert your data to Excel tables, you’re ready to add them to the Power Pivot data model. Follow these steps to add the newly created Excel tables to the data model using the Power Pivot tab:
1 Place the cursor anywhere inside the Customers Excel table.
2 Go to the Power Pivot tab on the Ribbon and click the Add to Data Model command.
Power Pivot creates a copy of the table and opens the Power Pivot window, shown in Figure 2-5.
Although the Power Pivot window looks like Excel, it’s a separate program altogether. Notice that the grid for the Customers table offers row numbers but no column references. Also notice that you cannot edit the data within the table. This data is simply a snapshot of the Excel table you imported.
Additionally, if you look at the Windows taskbar at the bottom of the screen, you can see that Power Pivot has a separate window from Excel. You can switch between Excel and the Power Pivot window by clicking each respective program on the taskbar.
FIGURE 2-5: The Power Pivot window shows all the data that exists in your data model.
Repeat Steps 1 and 2 in the preceding list for your other Excel tables: InvoiceHeader, InvoiceDetails. After you’ve imported all your Excel tables into the data model, the Power Pivot window will show each data set on its own tab, as shown in Figure 2-6.
FIGURE 2-6: Each table you add to the data model is placed on its own tab in Power Pivot.
Creating relationships between Power Pivot tables
At this point, Power Pivot knows that you have three tables in the data model but has no idea how the tables relate to one another. You connect these tables by defining relationships between the Customers, Invoice Details, and Invoice Header tables. You can do so directly within the Power Pivot window.
Follow these steps to create relationships between your tables:
1 Activate the Power Pivot window and click the Diagram View command button on the Home tab.The Power Pivot screen you see shows a visual representation of all tables in the data model, as shown in Figure 2-7. You can move the tables in Diagram view by simply clicking and dragging them.The idea is to identify the primary index keys in each table and connect them. In this scenario, the Customers table and the Invoice Header table can be connected using the CustomerID field. The Invoice Header and Invoice Details tables can be connected using the InvoiceNumber field.FIGURE 2-7: Diagram view allows you to see all tables in the data model.
2 Click and drag a line from the CustomerID field in the Customers table to the CustomerID field in the Invoice Header table, as demonstrated in Figure 2-8.
3 Click and drag a line from the InvoiceNumber field in the Invoice Header table to the InvoiceNumber field in the Invoice Details table.