Modifying Data Model from Power View

The Data Model in a Power View worksheet can be modified and improved upon by creating new tables in the workbook and defining new relationships among the tables. 

Step 1: Create a new table named Profit in the workbook.

Step 2: In the Power View1 worksheet, change the visualization from Matrix to Table.

Step 3: In the ALL tab of the Power View Fields, the new table Profit is now present. Click on the Power Pivot tab on the ribbon to open the Power Pivot window. Click Manage -> Diagram View. The Data Model is displayed.

Step 4: As we can see, the Profit table has no relationships. Now we have to add the Profit table to the Data Model:

• Go to the Power View1 sheet.
• In the Power View Fields pane, deselect the Month field from the Product table.
• Select the Annual Profit field from the Profit table.

Step 5: The Annual Profit field is showing incorrect values, as we currently do not have any relationship between the Profit table and other tables. Click on the CREATE button in the Power View Fields pane and a Create Relationship dialogue box appears.

Create a Relationship between the tables by picking the following tables and columns:

Table: Factory
Column (Foreign): Factory ID
Related Table: Profit
Related Column (Primary): Factory ID

Step 6: Open the Diagram View from the Power Pivot window.

We see that the new relationship is now displayed. The Data Model now consists of two relationships between the three tables in our workbook.

Step 7: Click on the arrow to the right of the field Annual Profit in the FIELDS section. Select the option Do Not Summarize from the dropdown menu.

Step 8: Arrange the fields in the FIELDS section, in the order of Region, Factory Name, Annual Profit, profit.

The Data Model gives us the ability to work with multiple tables and integrate them with each other using a specified relationship to build a relational database inside Microsoft Excel. It helps to work with larger datasets and provides us with a huge set of interactive features for creating, modifying, and managing datasets simply and easily. All these operations are performed with the help of Power Views in Excel, which enables the user to visualize the different operations that need to be performed on the data model.

In this example, we are going to use Power View with Data Model to create a relational database between different factories and the product. In order to learn, how to enable and use power view please, refer How to Enable and Use Power View in Excel?