# 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:

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:

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:

China dominates the list, not surprisingly.