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:

Training 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 ==>	Transforming data with Power Query (2 exercises)
Level ==>	Average difficulty
Subject ==>	Power Pivot training

Use Power Query in Excel to import and transform data on concert tours.

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 .

In a new Excel workbook, use the Get Data tool to import data from the Concert Tours.xlsx file in the folder shown above.

Import the Highest grossing tours worksheet and choose Transform Data when provided with the option.

Importing a worksheet from an Excel file in Power Query.

We need to massage the data before we can use it.

In Power Query, choose to remove the Ref column.

We don't need this column.

Split the Year(s) column into a Start year and End year column.

Splitting columns by delimiters in Power Query

We want these two separate columns.

Change the data type of the Actual gross, Adjusted gross and Average gross columns to Whole Number. This isn't quite as simple as it first appears...

Changing the data type of a column in Power Query.

There's at least once character in these columns which can't be converted into a number.

Load the results of the query into the Power Pivot data model. Apply formatting to the Actual gross, Adjusted gross and Average gross columns to show in U.S. dollars.