From time to time articles get published about the evolution of the Belgian house market. Interesting to learn about general trends but… sometimes you whished these analyses would be more tailored to your situation. It’s nice to know that prices for villas have stabilized last year, but does this trend also hold in my region? Sometimes we just have to do things our self…

PowerBI = Self Service BI

Lot’s of data is nowadays publicly available. The same holds for the Belgian house market analysis: http://statbel.fgov.be has a lot of public data sets available for analyzing different aspects of Belgium. One of them contains house sales information per village per year per house type (flat, house, villa). By diving into this data set we can find all sorts of detailed information.

But its not easy to navigate this data. We want to massage the data such that it becomes easier to filter and aggregate the data. To do so we can use many tools… but why leave Excel? In 2014 Microsoft released PowerBI, which amongst others contains Excel plugins for loading, cleaning and modeling data.

PowerQuery makes data loading easy

With the free PowerQuery plugin, we can easily create a script which downloads the data from the statbel website, filters and transforms the data until we become a representation which is easier to filter and aggregate:

If you want to give it a try, here is the M code, just copy it and paste it into a blank PowerQuery.

   1: let

   2:     Source = Excel.Workbook(Web.Contents("http://statbel.fgov.be/nl/binaries/NL_immo_statbel_jaar_131202_%20121715_tcm325-34189.xls")),

   3:     #"Per gemeente1" = Source{[Name="Per gemeente"]}[Data],

   4:     RemovedFirstRows = Table.Skip(#"Per gemeente1",2),

   5:     FirstRowAsHeader = Table.PromoteHeaders(RemovedFirstRows),

   6:     RemovedColumns = Table.RemoveColumns(FirstRowAsHeader,{"localiteit", "jaar_1", "oppervlakteklasse", "Column6", "P10 prijs(€)", "Q25 prijs(€)", "Q50 prijs(€)", "Q75 prijs(€)", "P90 prijs(€)", "Column16", "P10 prijs(€)_6", "Q25 prijs(€)_7", "Q50 prijs(€)_8", "Q75 prijs(€)_9", "P90 prijs(€)_10", "Column26", "P10 prijs(€)_15", "Q25 prijs(€)_16", "Q50 prijs(€)_17", "Q75 prijs(€)_18", "P90 prijs(€)_19", "Column36", "P10 prijs(€/m²)", "Q25 prijs(€/m²)", "Q50 prijs(€/m²)", "Q75 prijs(€/m²)", "P90 prijs(€/m²)", "gemiddelde prijs(€)", "gemiddelde prijs(€)_5", "gemiddelde prijs(€)_14", "gemiddelde prijs(€/m²)"}),

   7:     RenamedColumns = Table.RenameColumns(RemovedColumns,{{"aantal transacties", "House transaction count"}, {"totale prijs(€)", "House total cost"}, {"totale oppervlakte(m²)", "House building area"}, {"aantal transacties_2", "Villa transaction count"}, {"totale prijs(€)_3", "Villa total cost"}, {"totale oppervlakte(m²)_4", "Villa building area"}, {"aantal transacties_11", "Flat transaction count"}, {"totale prijs(€)_12", "Flat total cost"}}),

   8:     RemovedColumns1 = Table.RemoveColumns(RenamedColumns,{"totale oppervlakte(m²)_13"}),

   9:     RenamedColumns1 = Table.RenameColumns(RemovedColumns1,{ {"aantal transacties_20", "Ground transaction count"}, {"totale prijs(€)_21", "Ground total cost"}, {"totale oppervlakte(m²)_22", "Ground building area"}}),

  10:     Unpivot = Table.UnpivotOtherColumns(RenamedColumns1,{"refnis", "jaar"},"Attribute","Value"),

  11:     SplitColumnDelimiter = Table.SplitColumn(Unpivot,"Attribute",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Attribute.1", "Attribute.2"}),

  12:     ChangedType = Table.TransformColumnTypes(SplitColumnDelimiter,{{"Attribute.1", type text}, {"Attribute.2", type text}}),

  13:     RenamedColumns2 = Table.RenameColumns(ChangedType,{{"Attribute.1", "HouseType"}, {"Attribute.2", "Measurement"}}),

  14:     ChangedType1 = Table.TransformColumnTypes(RenamedColumns2,{{"Value", type number}}),

  15:     TransformedColumn = Table.TransformColumns(ChangedType1,{{"Measurement", Text.Proper}}),

  16:     RenamedColumns3 = Table.RenameColumns(TransformedColumn,{{"jaar", "Year"}}),

  17:     ChangedType2 = Table.TransformColumnTypes(RenamedColumns3,{{"Year", type date}}),

  18:     RenamedColumns4 = Table.RenameColumns(ChangedType2,{{"refnis", "NISCode"}})

  19: in

  20:     RenamedColumns4

PowerPivot to model the data the way you want it

Next we want to define useful calculations: Sums, averages, but also more challenging calculations, such as linking each village with the corresponding county. For this we use PowerPivot, a free addin for Excel 2010, out-of-the-box present in Excel 2013. Amongst others we define an average cost per house and an average cost per square meter calculation:

PowerView to build interactive charts

As soon as we have loaded and modeled our data, we can finally start building the reports we needed. The PowerView add-in in Excel 2013 allows us to quickly create interactive reports on top of our PowerPivot model. In less than two minutes we can create a report which shows the price evolution over time for the different counties, with a drill down into the actual villages, split by house type. Watch the video below to get an idea, or if you have Excel 2013, just download the Excel file and play with the report itself!

The nice thing about PowerView is that out-of-the-box it usually does what we expect it to do. For instance: when we click on a house type, the bar charts automatically show the average price for the selected type of house relative to the average price for all house types. For instance, in the county Brugge a flat is more expensive than the average price for any type of house, whereas in the other counties it is cheaper:
 

We can also create more advanced visualization. For instance a bubble chart plotting the average land price against the average size of the building area, and how this evolves over time. When you single click a county, you see the price evolution through time, double clicking will zoom in into the cities within that county.

Do it yourself

You can download the Excel sheet from my OneDrive at http://1drv.ms/1fcH2u6 and have fun with the data. Be sure to download the Excel file, since OneDrive does not allow you to interact with the data in the browser.

But if you want to learn how to build PowerPivot models and load data with PowerQuery, consider attending our three day course Building PowerPivot Models in Excel. If you are also interested in building PowerView and PowerMap reports on top of the PowerPivot models, you can instead attend the course Business Intelligence with Excel.

Have fun with the PowerBI self service business intelligence… the world is yours to discover!