Don’t wait for the opportunity CREATE it.
I was just trying to create an opportunity to work with Power BI; that was the initial plan. Later I created a session and I presented a version of this session at NTK Conference, Slovenia, next at SQL Supper and recently Channel 9 Techdays online. None of them was likewise, but the heart of the topic was same Power BI + Property Data.
I first looked at the price paid data a few years ago when Chris Webb presented a power query session in a London User group. Fascinating dataset and as usual Chris did the fantastic job showing nearly all cool things you can do with the dataset. I thought what else I could do with it.After few glasses of wine, I thought why the only price paid data why don’t I pull data from Zoopla. Then I added a bit of personal touch by adding schools and commuting information. I want to do a Power BI data story with Ofsted data and schools admission boundary (working mum syndrome 😉 )
That’s the story behind the session thought process then after I presented some people asked me to share the PBI Model, and some asked me how I extracted data from different data sources. So here comes the blog post which I promised long ago …
The data story:
Schools Data:
First I extracted Ofsted data. This database has all the information related to schools like number of pupils, when did the last time inspection happen, etc. It was a simple single file. After I had imported files into Power BI, I can see I had an extra row, and I just clicked on the first row as column step, twice, and it was sorted. This two click Power BI task will be tedious in any other ETL tool. M is amazing !
Price Paid Data:
Next data set was price paid data. All price paid data, and additional sale information freely available to download from the Land Registry Price Paid data, and it gets monthly updated. Again a simple CSV file.
NEXT comes the cool bit…
BING Maps API:
I need to confess; this idea was purely stolen from Chris Webb, M presentation from London Power BI user group. He showed how to use Google Maps to find the distance, and I used to BING Maps API. Again, it is very easy. You can get API key by going to BING Maps Developer site. I went for transit option to find the commute time. I used Power Bi WEB data source with following URL
I used M function to find the duration.I am going to use this function in my next dataset to find the distance from my preferable postcode.
M code:
let FindDuration = (FromPostCode as text)=> let Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/V1/Routes/Transit?wp.0="&FromPostCode&"&wp.1=WC2H8HR&timeType=Departure&dateTime=9:00:00PM&output=xml&key=YourAPIKeyGoesHere")), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}), ResourceSets = #"Changed Type"{0}[ResourceSets], ResourceSet = ResourceSets{0}[ResourceSet], #"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}), Resources = #"Changed Type1"{0}[Resources], Route = Resources{0}[Route], #"Changed Type2" = Table.TransformColumnTypes(Route,{{"Id", type text}, {"DistanceUnit", type text}, {"DurationUnit", type text}, {"TravelDistance", type number}, {"TravelDuration", Int64.Type}, {"TravelDurationTraffic", Int64.Type}, {"TrafficDataUsed", type text}, {"TrafficCongestion", type text}}), #"Expanded RouteLeg" = Table.ExpandTableColumn(#"Changed Type2", "RouteLeg", {"ActualEnd", "ActualStart", "Cost", "EndLocation", "EndTime", "ItineraryItem", "RouteRegion", "RouteSubLeg", "StartLocation", "StartTime", "TravelDistance", "TravelDuration"}, {"RouteLeg.ActualEnd", "RouteLeg.ActualStart", "RouteLeg.Cost", "RouteLeg.EndLocation", "RouteLeg.EndTime", "RouteLeg.ItineraryItem", "RouteLeg.RouteRegion", "RouteLeg.RouteSubLeg", "RouteLeg.StartLocation", "RouteLeg.StartTime", "RouteLeg.TravelDistance", "RouteLeg.TravelDuration"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded RouteLeg",{"TravelDistance", "TravelDuration"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "DurationInHours", each ([TravelDuration]/60)/60), #"Inserted Rounding" = Table.AddColumn(#"Added Custom", "Inserted Rounding", each Number.Round([DurationInHours], 2), type number), #"Renamed Columns" = Table.RenameColumns(#"Inserted Rounding",{{"Inserted Rounding", "TransitDuration"}}) in #"Renamed Columns" in FindDuration
ZOOPLA API:
You can create Zoopla API account from Zoopla Developer API site, pretty simple API. With the power of Power BI, I quickly imported data using WEB data source from Power BI desktop. Here is the URL I used
I have given different parameters; you can get all parameters list from here. Once I imported the data, I renamed and expanded all the required columns. I used FindDuration function from the above query to find the distance from each property
Date Table:
I created the date table by following blog post by Matt Masson. There are different ways to do it; I preferred it
I imported few other datasets like UK postcodes data for maps and to relate different data sets. Also recession data, I created a table with static values just to show the recession period in the pulse chart.
Finally, my data model looked like this
And I managed to do some cool dashboards with this data. You can have a look at the reports from https://goo.gl/TqPUoo or some screenshots below
Creating this model was super easy, finding all those data sources were tricky and editing all the properties of different visualisations was time-consuming, but rest was simple. I chose this topic to present to show how easy it is to work with data from various data sources and create stunning visualisations in a jiffy.
Let me know what you think?
Power BI Model – https://goo.gl/mkiGLh
Prathy??
2 comments
This is great! Now I need to find something similar for the US!
This is great! Now I need to find something similar for the US!