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:

Learn Excel

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

Apply basic transforms to a table of tallest buildings to tidy up the data.

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 Buildings.xlsx in a location of your choice.

Choose to import data from the Tallest Buildings.xlsx file from the folder shown at the top of this exercise.

Select the Buildings worksheet and choose to transform the data.

Choosing to transform data in Power Query for Excel

Click Transform Data to open Power Query.

In the Applied Steps list, delete any steps after the Navigation step

Deleting a query step in Power Query for Excel

Leave the Source and Navigation steps but delete any others.

Add a query step to remove the first row of the table.

We don't need the first row of this table.

Add a step to use the first row as column headers.

The columns should have sensible names.

Power Query may automatically add a Changed Type step to assign data types to each column.

Add a step to remove the Image and Ref columns from the table.

The Image column is empty so there's no point in keeping it.

Find the Floors column. We want to show the floors above ground and floors below ground in separate columns.

Columns of data in Power Query for Excel

The original column looks like this.

Add a Split Column transform to the Floors column using the leftmost space as a delimiter.

You should now have two columns.

Select the Floors.2 column and add a step to Extract Text Between Delimiters, using a space as both the start and end delimiters.

Columns with extracted values in Power Query

You can find the Extract option in the Transform tab of the Power Query ribbon.

Rename both Floors columns and assign a Whole Number data type to each.

Renamed columns with changed data types in Power Query

The final columns should look like this.

Choose to close and load the query to a table in a new Excel worksheet.

An Excel table with data on tallest buildings

The final results.

Save and close the workbook.

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

Excel 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

EXCEL EXERCISES

EXCEL EXERCISES

Apply basic transforms to a table of tallest buildings to tidy up the data.

Head office

London

Manchester