This page has 0 threads | Add post

Exercises

Many people around the world enjoy and benefit from our exercises. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

You can find other training resources for the subject of this exercise here:

Power Pivot classes

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Software ==>	Power Pivot (43 exercises)
Topic ==>	Power Pivot data models (2 exercises)
Level ==>	Average difficulty
Subject ==>	Power Pivot training

Use Power Pivot to import multiple worksheets from an Excel file and create a data model

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.

Before you can do this exercise, you'll need to download and unzip this file .

Create a new Excel workbook and open Power Pivot. Import data from the Excel file called Music Tours.xlsx in the folder shown above.

Choosing to import data from an Excel workbook into a Power Pivot data model

Don't forget to use the first row as column headers.

Choose to import every table but exclude the columns listed below:

Table name	Columns to exclude
Album	Wiki URL Cover art
Country	Flag URL
Parent Company	Logo URL
Record Label	Logo URL

Switch to the Diagram View and create the relationships for the artists, albums and related tables as shown in the diagram below:

Look for columns with matching names for clues to which relationships to create.

Now add the relationships for the artists, tours and related tables as shown below:

A data model diagram in Power Pivot showing relationships between tables

Feel free to arrange the tables any way you like.

Hide all ID columns from client tools.

Apply formatting to the following date fields so that they don't display the time:

Table name	Column names
Album	Release date
Show	Show date
Tour	Start date End date
Track	Single release date
Venue	Construction start date Opening date Closing date Demolition date

Apply currency formatting to the following columns to display in US dollars:

Table name	Column names
Show	Revenue $
Tour	Tour gross $
Venue	Construction cost $m

Create a pivot table as shown in the diagram below:

A pivot table from a Power Pivot data model

We've sorted the results in descending order of total tickets sold.

Save and close the file.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

Power Pivot training classes

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This page has 0 threads

Add a new post

EXERCISE TOPIC

POWER PIVOT EXERCISES

POWER PIVOT EXERCISES

Use Power Pivot to import multiple worksheets from an Excel file and create a data model

Head office

London

Manchester