Part V: Understanding Power Pivot and Power Query
FIGURE 36.2
This table provides information on employees: first name, last name, and job title.
You need to create an analysis that shows sales by job title. This would normally be difficult
given the fact that sales and job title are in two separate tables. But with the internal data
model, it’s just a matter of a few clicks.
The first thing you will need to do is to convert your data tables into Excel Table objects
that the internal data model can recognize:
- Click inside the TransactionMaster data table and select Insert ➪ Table. This
activates the Create Table dialog box.
- Select the table range and then click OK. You will want to name your Table
objects explicitly. This way, you can easily recognize your tables in the internal
data model. If you don’t name your tables, the internal data model shows them as
Table1, Table2, and so on.
- Insert your cursor anywhere inside the table and select the Table Tools Design
tab. There you will find the Table Name input box in the Properties group. Enter a
new name for your table (in this case, Transactions). At this point, you’re ready to
feed your table to the internal data model.
- Select Data ➪ Existing Connections.
- In the Existing Connections dialog box, select the Tables tab. You will see a list
of existing Table objects (see Figure 36.3).
- Double-click the Transactions Table object. This will activate the Import Data
dialog box shown in Figure 36.4.
- Select the Only Create Connection option and then click OK.
Repeat the previous steps for the Employees table.
Once both tables are loaded into the internal data model, you can create the needed
PivotTable by following these steps:
- Select Data ➪ Existing Connections.
