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.
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 :
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.
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.
Similarly, Choose Order_id from the Order dataset and Return dataset.
Step 5: You can check Your relationship by going to manage the relationship.
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.
It will bring you to a new slide inside your excel file.
From Pivot Table Fields, Drag “Region” from the Order dataset to Rows.
Then, From the product dataset, Drag 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.