This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	PowerPivot (72 exercises)
Topic ==>	Transforming data (Power Query) (4 exercises)
Level ==>	Harder than average
Subject ==>	Power BI training

For a table of skyscrapers, perform lots of query transforms and then pivot the results

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 workbook, then use Power Query to query the workbook called Tables in the above folder:

Choose to query this table.

Before you do anything else, make sure you tell Power Query you want to use the first row as column headers.

Apply suitable query steps (the answer has about 20 in) to turn the data into this:

We've split the pinnacle height into metres and feet, and created a new metres-per-floor column (a few of the values for which will be null).

Getting rid of the m and ft suffices proved tricky, as the character before them doesn't appear to be a space; try removing the m and ft letters, then applying a trim transform (it's on the TRANSFORM tab).

Now get rid of all but these columns:

We're going to show the number of buildings by country and completion year.

Apply two more query steps:

Filter the list to show only those buildings whose completion date is not null.
Sort the data by completion year (this will ensure the pivot table column headings will be in the correct order).

Pivot the data using the TRANSFORM tab of the ribbon to get: