Open In App

Excel Power Pivot – Managing Data Model

Improve
Improve
Like Article
Like
Save
Share
Report

Power Pivot is something that helps us in relating between two different data sets which are in two different worksheets. We can manage and relate any type of data using Power Pivot. It is used for data analysis and creates many different data models. we can collect large data from different sheets and can apply or perform any relation between them. Power Pivot is usable for tables only. If we have any dataset then we need to convert that dataset into tables before using Power Pivot.

Managing Data Model

When data is formed by combining various sources, then those types of data are formed by  Data Modals. Basically, it is created by an existing relationship between two or more tables. It is created automatically when we import two or more tables from a database. Let’s understand how to Manage Data Models using Excel Power Pivot with the help of an example,

Step 1: Open Ms-Excel and save it as any name let’s save it as “Managing Data Model”. Then we need to take any dataset, Let’s say we take, three different datasets whose name is Orders, Products, and Return respectively. and our task is to manage the whole data in Power Pivot and find the Sum of Sales in each Region.  

Orders-dataset

 

Product-dataset

 

Return-dataset

 

Step 2: Now, before going to power pivot we need to convert this dataset into tables because Power Pivot is usable for Tables only.  So, convert this dataset into tables by simply selecting any cell of the Order data set -> then pressing ctrl + t -> then selecting OK. Similarly, repeat these steps to convert them into Tables for the Product dataset and Return dataset.  When you convert your dataset into tables then it will show like images mentioned below : 

Table-of-orders

 

Table-of-products

 

Table-of-return

 

Step 3: Now, we need to understand that, our aim is to find the Sum of Sales in each region. So the region column is in the first dataset i.e: Orders. And Sales Column is in the second dataset i.e; Products so, here PowerPivot came in the role to bring them in a single comparison. To do that, first rename all the table names From Table1, Table2, and Table3 to Orders, Products, and Return respectively. To rename a table name just click on any cell of that particular table -> go to Table design -> Rename table name at the top left corner.

Power-pivot-for-options

 

Power-pivot-for-products

 

Power-pivot-for-return

 

Step 4: Then go to Option Slide -> go to Power Pivot -> Choose “Add to Data Model” -> then go back to Excel -> Now, Go to Products slide -> then repeat the same step for Product slide also -> then repeat the same step for return slide. The common column between the Order dataset and the product dataset is product_id and the common column between the ordered dataset and return dataset is Order_id.  

Open the previous Power Pivot Slide of Order tab -> go to the design tab -> Create Relationship -> Choose Product_id from Order Dataset and Product dataset.

Product-id-from-Order-dataset

 

Product-id-from-product-dataset

 

Similarly, Choose Order_id from the Order dataset and Return dataset. 

Order-id-from-Order-and-Return-dataset

 

Step 5: You can check Your relationship by going to manage the relationship.

Manage-relationships

 

Here, you can see that both relationships are active. 

Step 6: At last go to the Home tab -> Choose Pivot Table -> it will show you a pop-up -> press ok -> it will bring you to a new slide inside your previous Excel file.

Pressing-ok

 

It will bring you to a new slide inside your excel file.

New-slide

 

From Pivot Table Fields, Drag “Region” from the Order dataset to Rows.

Dragging-region-to-rows

 

Then, From the product dataset, Drag Sales to Values.

Dragging-sales-to-values

 

So by doing this way we managed our data using Power Pivot. In the last above-mentioned image we can see that it is showing the sales done in each region, which is impossible to do without using Power Pivot because Region and sales columns are in two different datasets, if we try to do it with a normal Pivot table then it not be happening, a normal pivot table can be used if these two columns of regions and sales are present in any single dataset. 


Last Updated : 04 Dec, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads