Excel Power Pivot - Managing Data Model
Last Updated : 10 Jun, 2024
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.
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 names are 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.
Dataset of Order
Dataset of Products
Dataset of Return
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 :
Conversion of Order Dataset into the table
Conversion of Product Dataset into the Table
Conversion of the Return Dataset into the Table
Step 3: Now, we need to understand that, we aim 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.
Rename Table 1 to Orders
Rename Table 2 to the Product
Rename Table 3 to 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.
Choosing Product_id from the Ordered Dataset
Choosing Product_id from the Product Dataset
Similarly, Choose Order_id from the Order dataset and Return dataset.
Choosing Order_id from the Ordered 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.
Managing Relationship
Users can edit or delete an existing relationship in the data model.
Steps to see all the Existing Relationships in the Data Model
Step 1: Click the design tab in the Power pivot window.
Step 2: In the Relationship group, click on Manage the Relationship.
A dialog box appears with all the existing relationships in the data model.
Steps to Edit a Relationship
Step 1: Select the relationship you want to edit.
Step 2: Select the edit button. A dialog box appears for the edit relationship.
Step 3: Now you can make the required changes.
Step 4: The required changes will reflect on your data after clicking "OK".
Steps to Delete the Relationship
Step 1: Select the relationship you want to delete.
Step 2: Select the delete button.
Note: A alert message will pop up showing how the affected tables will affect the reports after deleting the relationship.
Step 3: Click Ok if you surely want to delete that relationship. The relationship is deleted.
Similar Reads
Loading Data with Power Pivot in Excel There are two ways to input data into Power Pivot: Data may be immediately loaded into PowerPivot, populating the database, or it can be loaded into Excel and added to the Data Model. You may either create connections and/or use the existing connections to import data into the Power Pivot Data Model
5 min read
Data Models in Excel Power View 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, modify
5 min read
Exploring Data with Excel Power Pivot Power Pivot is an Excel one can use to perform intense information investigation and make modern information models. With Power Pivot, we can squash up enormous volumes of information from different sources, perform data examination quickly, and share experiences without any problem. In both Excel a
7 min read
Power Pivot for Excel Power Pivot serves as an Excel add-on enabling robust data analysis and the creation of advanced data models. This tool facilitates the integration of extensive data from diverse sources, enabling swift information analysis and seamless sharing of insights. Whether working in Excel or Power Pivot, u
10 min read
Data Modeling in Power BI Data modeling is the process of identifying, organizing and defining the types of data a business collects and the relationships between them. It uses diagrams, symbols and textual definitions to visually represent how data is captured, stored and used. A well-designed data model helps:Understand da
6 min read