POWER BI EXERCISES▼
- PowerPivot data models (7)
- Pivot tables using PowerPivot (2)
- Using Excel tables (3)
- Using other data sources (1)
- Transforming data (Power Query) (7)
- Calculated columns (7)
- Measures (2)
- The CALCULATE function (15)
- More advanced DAX functions (5)
- Calendars (1)
- Date functions (10)
- Hierarchies (2)
- KPIs (5)
- Power View (4)
- Power BI Desktop overview (3)
- Power BI Desktop maps (1)
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.
You can learn how to do this exercise on the relevant Wise Owl classroom training course (sadly for the moment only in the UK).
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:
- 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:
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.