PowerPivot | Transforming data (Power Query) exercise | Transform a table of buildings, including pivoting

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.

Software ==> PowerPivot  (75 exercises)
Version ==> Excel 2013 and later
Topic ==> Transforming data (Power Query)  (7 exercises)
Level ==> Harder than average
Subject ==> Power BI training
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Create a new workbook, then use Power Query to query the workbook called Tables in the above folder:

Tallest buildings

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:

Cleansed data

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:

Three columns remaining

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


Apply two more query steps:

  1. Filter the list to show only those buildings whose completion date is not null.
  2. 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:

The final pivot table

China dominates the list, not surprisingly.

Load this data into Excel.

Although the column and row headings come into Excel, the values don't appear to.  Does anybody have any theories for why?

Save this workbook as Pivotal moment, then close it down.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post