Excel Power Pivot - Managing Data Model

The major use of Power Pivot is its abipty to manage the data tables and the relationships among them, to faciptate analysis of the data from several tables. You can add an excel table to the Data Model while you are creating a PivotTable or directly from the PowerPivot Ribbon.

You can analyze data from across multiple tables only when relationships exist among them. With Power Pivot, you can create relationships from the Data View or Diagram View. Moreover, if you had chosen to add a table to the Power Pivot, you need to add a relationship as well.

Adding Excel Tables to Data Model with PivotTable

When you create a PivotTable in Excel, it is based only on a single table / range. In case you want to add more tables to the PivotTable, you can do so with the Data Model.

Suppose you have two worksheets in your workbook −

One containing the data of salespersons and the regions they represent, in a table- Salesperson.

Another containing the data of sales, region and month wise, in a table – Sales.

You can summarize the sales – salesperson-wise as given below.

Cpck the table – Sales.

Cpck the INSERT tab on the Ribbon.

Select PivotTable in the Tables group.

An empty PivotTable with the fields from the Sales table – Region, Month and Order Amount will be created. As you can observe, there is a MORE TABLES command below the PivotTable Fields pst.

Cpck on MORE TABLES.

The Create a New PivotTable message box appears. The message displayed is- To use multiple tables in your analysis, a new PivotTable needs to be created using the Data Model. Cpck Yes

A New PivotTable will be created as shown below −

Under PivotTable Fields, you can observe that there are two tabs – ACTIVE and ALL.

Cpck the ALL tab.

Two tables- Sales and Salesperson, with the corresponding fields appear in the PivotTable Fields pst.

Cpck the field Salesperson in the Salesperson table and drag it to ROWS area.

Cpck the field Month in the Sales table and drag it to ROWS area.

Cpck the field Order Amount in the Sales table and drag it to ∑ VALUES area.

The PivotTable is created. A message appears in the PivotTable Fields – Relationships between tables may be needed.

Cpck the CREATE button next to the message. The Create Relationship dialog box appears.

Under Table, select Sales.

Under Column (Foreign) box, select Region.

Under Related Table, select Salesperson.

Under Related Column (Primary) box, select Region.

Cpck OK.

Your PivotTable from the two tables on two worksheets is ready.

Further, as Excel stated while adding the second table to the PivotTable, the PivotTable got created with Data Model. To verify, do the following −

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Manage in the Data Model group. The Data View of the Power Pivot appears.

You can observe that the two Excel tables that you used in creating the PivotTable are converted to data tables in the Data Model.

Adding Excel Tables from a Different Workbook to Data Model

Suppose the two tables – Salesperson and Sales are in two different workbooks.

You can add the Excel table from a different workbook to the Data Model as follows −

Cpck the Sales table.

Cpck the INSERT tab.

Cpck PivotTable in the Tables group. The Create PivotTable dialog box appears.

In the Table/Range box, type Sales.

Cpck on New Worksheet.

Check the box Add this data to the Data Model.

Cpck OK.

You will get an empty PivotTable on a new worksheet with only the fields corresponding to the Sales table.

You have added the Sales table data to the Data Model. Next, you have to get the Salesperson table data also into Data Model as follows −

Cpck on the worksheet containing Sales table.

Cpck the DATA tab on the Ribbon.

Cpck Existing Connections in the Get External Data group. The Existing Connections dialog box appears.

Cpck on the Tables tab.

Under This Workbook Data Model, 1 table is displayed (This is the Sales table that you added earper). You also find the two workbooks displaying the tables in them.

Cpck Salesperson under Salesperson.xlsx.

Cpck Open. The Import Data dialog box appears.

Cpck on PivotTable Report.

Cpck on New worksheet.

You can see that the box – Add this data to the Data Model is checked and inactive. Cpck OK.

The PivotTable will be created.

As you can observe the two tables are in the Data Model. You might have to create a relationship between the two tables as in the previous section.

Adding Excel Tables to Data Model from the PowerPivot Ribbon

Another way of adding Excel tables to Data Model is doing so from the PowerPivot Ribbon.

Suppose you have two worksheets in your workbook −

One containing the data of salespersons and the regions they represent, in a table – Salesperson.

Another containing the data of sales, region and month wise, in a table – Sales.

You can add these Excel tables to the Data Model first, before doing any analysis.

Cpck on the Excel table - Sales.

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Add to Data Model in the Tables group.

Power Pivot window appears, with the data table Salesperson added to it. Further a tab – Linked Table appears on the Ribbon in the Power Pivot window.

Cpck on the Linked Table tab on the Ribbon.

Cpck on Excel Table: Salesperson.

You can find that the names of the two tables present in your workbook are displayed and the name Salesperson is ticked. This means the data table Salesperson is pnked to the Excel table Salesperson.

Cpck Go to Excel Table.

Excel window with worksheet containing Salesperson table appears.

Cpck the Sales worksheet tab.

Cpck the Sales table.

Cpck Add to Data Model in the Tables group on the Ribbon.

The Excel table Sales is also added to the Data Model.

If you want to do analysis based on these two tables, as you are aware, you need to create a relationship between the two data tables. In Power Pivot, you can do this in two ways −

From Data View

From Diagram View

Creating Relationships from Data View

As you know that in Data View, you can view the data tables with records as rows and fields as columns.

Cpck on the Design tab in the Power Pivot window.

Cpck on Create Relationship in the Relationships group. The Create Relationship dialog box appears.

Cpck on Sales in the Table box. This is the table from where the relationship starts. As you are aware, Column should be the field that is present in the related table Salesperson that contains unique values.

Cpck on Region in the Column box.

Cpck on Salesperson in the Related Linked Table box.

The Related Linked Column gets automatically populated with Region.

Cpck the Create button. The relationship is created.

Creating Relationships from Diagram View

Creating Relationships from Diagram View is relatively easier. Follow the given steps.

Cpck the Home tab in the Power Pivot window.

Cpck Diagram View in the View group.

The Diagram View of the Data Model appears in the Power Pivot window.

Cpck on Region in Sales table. Region in Sales table is highpghted.

Drag to Region in Salesperson table. Region in Salesperson table is also highpghted. A pne appears in the direction you dragged.

A pne appears from the table Sales to the table Salesperson indicating the relationship.

As you can see, a pne appears from the Sales table to the Salesperson table, indicating the relationship and the direction.

If you want to know the field that is a part of a relationship, cpck on the relationship pne. The pne and the field in both the tables are highpghted.

Managing Relationships

You can edit or delete an existing relationship in Data Model.

Cpck the Design tab in the Power Pivot window.

Cpck Manage Relationships in the Relationships group. The Manage Relationships dialog box appears.

All the relationships that exist in the Data Model are displayed.

To edit a relationship

Cpck on a Relationship.

Cpck the Edit button. The Edit Relationship dialog box appears.

Make the required changes in the relationship.

Cpck OK. The changes get reflected in the relationship.

To delete a relationship

Cpck on a Relationship.

Cpck on the Delete button. A warning message appears showing how the tables that are affected by deleting the relationship would affect the reports.

Cpck OK if you are sure you want to delete. The selected relationship is deleted.

Refreshing Power Pivot Data

Suppose you modify the data in the Excel table. You can add / change / delete the data in the Excel table.

To refresh the PowerPivot data, do the following −

Cpck the Linked Table tab in the Power Pivot window.

Cpck Update All.

The data table is updated with the modifications made in the Excel table.

As you can observe, you cannot modify data in the data tables directly. Hence, it is better to maintain your data in Excel tables that are pnked to the data tables when you add them to the Data Model. This faciptates updating the data in data tables as and when you update the data in Excel tables.