POWER BI EXERCISES▼
- PowerPivot data models (7)
- Pivot tables using PowerPivot (2)
- Using Excel tables (3)
- Using other data sources (1)
- Transforming data (Power Query) (7)
- Calculated columns (7)
- Measures (2)
- The CALCULATE function (15)
- More advanced DAX functions (5)
- Calendars (1)
- Date functions (10)
- Hierarchies (2)
- KPIs (5)
- Power View (4)
- Power BI Desktop overview (3)
- Power BI Desktop maps (1)
PowerPivot | PowerPivot data models exercise | Create a clean data model, and a pivot table based on it
This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.
You can learn how to do this exercise on the relevant Wise Owl classroom training course (sadly for the moment only in the UK).
You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
If you haven't already done so, run the SQL script in the above folder in SQL Server Management Studio to generate a database (not for commercial use or copying) called MAM.
In a new workbook, in PowerPivot import from this MAM database the following tables:
The aim of this exercise is to create the following pivot table:
This pivot table shows that Sutton Coldfield and Burnley are the towns with the highest average quantity purchased.
To help you do this, create the following data model:
The data model should contain well-named tables, and no unnecessary details. See if you can also import only the bare minimum of fields that you need to get the pivot table to work.
Whoops! We've forgotten to add in the tblProduct table. Go back into PowerPivot and add this table, but only importing products where the full price is £15 or more.
You'll also need to create a relationship between the product and transaction tables, and may also need to change the properties of the transaction table to add in the ProductId column so that you can link to it.
Amend your data model and pivot table so that they look like this:
The revised data model, with the product name field.
Amend your pivot table so that it shows the number of transactions, sorted by town name:
The (blank) column shows all of the transactions which don't have a matching product (because we didn't import it).
Save this workbook as Data models are fun, and close it down.