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:

Microsoft Excel classes

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

Software ==>	Excel (128 exercises)
Topic ==>	Querying data (18 exercises)
Level ==>	Average difficulty
Subject ==>	Excel training

Use Power Query to clean data sources related to music.

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 blank Excel workbook and save it as Transforming Music.xlsx.

Choose to import data from the Concert Tours.xlsx file in the folder shown at the top of this exercise.

Load the Highest grossing tours and Most attended tours worksheets to tables in separate worksheets.

You should have two queries loaded into separate worksheets.

Edit the Highest grossing tours query in Power Query.

Add a step to remove the first row from the table.

The first row is empty so we don't need it.

Delete the Ref column, then move the Artist and Tour title columns to the right of the Peak column.

Multiple columns in Power Query for Excel

Put the first four columns in this order.

Remove the $ sign from the Actual gross, Adjusted gross and Average gross columns and make sure the data type of each is set to Whole number.

Number columns in a query in Power Query for Excel

You can quickly remove the $ symbol using a Replace Values transform.

Remember that you can select multiple columns to apply the same to transform to several columns at once.

Split the Year column into two separate columns called Start year and End year.

A column in Powe Query after a split column transform

You can create these columns in many different ways, choose your favourite!

Close and load the changes you've made to the Highest grossing tours query.

Now edit the Most attended tours query, performing the following transforms:

Remove the Ref column.
Split the Year(s) column into a Start year and End year column.
Modify the Tickets sold column to return only the number of tickets.
Filter the table for tours which started after the year 2020 and had at least 100 shows.

Close and load the changes to the query.

An Excel table created from a query in Power Query.

The results you should see.

Close and save the file.

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

Microsoft Excel training courses

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

EXCEL EXERCISES

EXCEL EXERCISES

Use Power Query to clean data sources related to music.

Head office

London

Manchester