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:

Classes in Power Pivot

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 ==>	Relatively easy
Subject ==>	Power Pivot training

Import three worksheets from an Excel workbook into a Power Pivot 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.

Choose to import data from the Excel file called Tallest Buildings.xlsx in the folder shown above.

Importing data from an Excel workbook in Power Pivot

Remember to check the box for column headers.

Choose to import all three worksheets from the Excel file.

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

Feel free to alter the friendly name of each table.

Switch to the Diagram View and create relationships between the three tables as shown in the diagram below.

A data model diagram in Power Pivot showing relationships between three tables.

Columns with the same name in different tables should be related.

Choose to hide all the ID columns except for Building ID from client tools.

Hiding fields from client tools in a Power Pivot data model

Hide the highlighted fields.

Switch to the Data View and format the Height m column to display two decimal places.

Formatting a column of numbers in a Power Pivot data model

Use the Home tab of the Power Pivot ribbon to format the column.

From Power Pivot choose to create a pivot table on a new Excel worksheet. Configure the pivot table as shown in the diagram below:

A pivot table in Microsoft Excel using tables from a Power Pivot data model

The pivot table uses fields from each of the three tables in the data model.

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:

Classes in Power Pivot

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

Import three worksheets from an Excel workbook into a Power Pivot data model

Head office

London

Manchester