Power BI | Calculated columns exercise | Use calculated columns to create a data model of house prices

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 ==> Power BI  (111 exercises)
Version ==> Latest update
Topic ==> Calculated columns  (8 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 Power BI report, and load both worksheets from the house prices workbook in the above folder:

Separate tables

Initially the tables aren't joined together. The data show house price sales for the UK for 2015-16, if you're interested!

Use Query Editor to use the first Property types row as a header row, then create a relationship to get something like this:

Linked tables

Better - now we've got something to work with.

Now hide tables and columns and create lots of calculated columns to get your field list looking like this:

Calculated columns

See the notes below for what each of these columns should display.

This should enable you to show the following matrix:

The final matrix

Again, see the notes below for what your fields should display.

Here's what the fields should show:

Field What is should show
Completion year The year each property sale was completed.
Desirability If the property is either a new build or freehold, this should show Desirable; otherwise it should show Not Desirable.
Sales band For properties costing up to £200k, this should show Affordable; for properties between £200k and £500k this should show Not too bad; for properties between £500k and £1,000k this should show Expensive; otherwise this should show Millionaire mansion
Type of property This should show the description from the Property type table.

The hardest bit about this is probably getting the sales bands to appear in the correct order in the left-hand column of the matrix.

Save this report as Neat work, 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