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

Use the Power Query editor to clean a table of the world's tallest buildings using various transforms

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 from the ribbon choose Data | Get Data | From File | From Excel Workbook

Choose the Excel file called Tallest Buildings.xlsx in the folder shown above. Select the Buildings worksheet and choose Transform Data.

Importing a table into an Excel workbook using Power Query.

The data is a bit too messy to use at the moment - we need to transform it to make it usable.

In the Power Query editor, choose to remove the Image and Ref columns.

Removing a column in the Power Quey editor in Microsoft Excel

You can right-click a column header and choose to remove it.

Select the Floors column and choose to split the column using a delimiter.

Splitting a column by a delimiter in the Power Query editor

Use this option in the Home tab of the ribbon.

Use a space as the delimiter and split the column once at the leftmost space.

Splitting a column by a space delimiter in the Power Query editor.

This will create two columns.

Rename the Floors.1 column as Floors above ground.

Double-click a column header to rename it.

Split the Floors.2 column by Digit to Non-Digit and then split it again by Non-Digit to Digit. Remove the columns either side of the numbers you have extracted

A column after a Split transform in Power QUery

After splitting the column twice, you should be left with three columns - we want only the one containing numbers.

Apply a Replace Values transform to the remaining column of numbers to fill in the blanks with zeros.

Using the Replace Values transform on a column in Power Query

Replace the null values with 0.

Rename the column as Floors below ground and change its data type to Whole Number.

Changing the data type of a column in Power Query

Click the icon next to the column name to choose a new data type.

Choose Home | Close & Load | Close & Load To in the Power Query ribbon and choose to create a connection only, adding the data to the Power Pivot data model.