Creating data models in PowerPivot is an important step because if you want to work with any data, you’re going to have to know to work with data models.
However, if data models sounds way too advanced, then check out this Pivot Tables for Beginners video tutorial.
Pivot Table Excel download file
The next thing your should do is download the above exercise file. By using the above Excel exercise file, which has been created by importing data from an Access database, you can follow the steps below.
The Excel file consists of 4 sheets, that can be seen below, we will first need to connect these together into a data model. Therefore, before you start connecting, it would be a good idea to install PowerPivot.
Creating Data Models in PowerPivot – Step by Step
Ensure that you have PowerPivot Installed or Enabled
Try the following:
- First, open Excel and then click on the PowerPivot tab.
(If you can’t find the PowerPivot tab then you may have to install it)- File – Options – Add ins – Com Add ins (from drop down menu)
If you want to know more then check out this how to install PowerPivot tutorial.
- File – Options – Add ins – Com Add ins (from drop down menu)
Now that you’ve installed PowerPivot then you can continue.
- Firstly, click on the FactInternetSales tab. (This has a table called FactInternetSales).
- Secondly, Click anywhere on the Table.
- Finally, from the Power Pivot Tab, click Add to Data Model.
- As a result the FactInternetSales Table has been added to the data model.
- Now swap back to Excel and do the same for the following fields.
- DimProduct
- DimProductSubCategory
- DimProductCategory
- As a result the tabs at the bottom of the PowerPivot window should now look like.
- Next, it’s time to create the data model. So click on the Diagram View Button at the top of the screen.
- Further, in the diagram view, you need to arrange the tables to look like.
- and, using the mouse, drag from ProductKey in the DimProduct Table to ProductKey in the FactInternetSales table. A line will appear between the two.
- Do the same for:
- DimProduct – DimProductSubcategory with ProductSubcategoryKey
- DimProductSubcategory – DimProductCategory with ProductCategoryKey
- Your Diagram view should now be completed and, in addition, you have created your first data model in PowerPivot.
Create a PivotTable from the PowerPivot Data Model
In addition to creating our data model we should also create a PivotTable. Because at the moment it may seem that you’ve done a lot of work for not much come-back. Therefore, let’s create a Pivot Table that would ordinarily be impossible to do without VLookups. As a result you will see why PowerPivot is a powerful tool.
- First of all, click on the PivotTable button, which is at the top of the screen.
- Then Click on OK. (You should ensure that the New Worksheet button is selected).
- Now you can create a PivotTable from separate tables:
- Drag Sales Amount from the FactInternetSales table to Values
- Drag EnglishProductCategoryName from the DimProductCategory to Rows
- Format the Sales Amount numbers as Accounting (Right click – Value Field Settings – Number Format – Accounting then OK out of the boxes)
- There you go! You see miracles do happen.
In conclusion, knowing how to not only import data from Excel to create data models is a vital skill. As a result you will be able to create PivotTables that just weren’t possible before. Now, you’ve added Tables to your data model you can’t use regular calculated fields, you have to use DAX formulas.
Excel Training
We are delighted to announce a one-day Advanced Excel 365 training course exclusively designed for businesses. This intensive programme is focused on Excel 365 skills and aims to provide significant benefits to individuals within your department or company.
By participating in this course, attendees will have the opportunity to enhance their Excel proficiency, enabling them to maximise productivity, streamline data analysis, and make more informed decisions. We highly recommend that your team take advantage of this valuable training opportunity to excel in their roles and contribute to the success of our organisation.