Fun with Power Query and public data from Copenhagen Open data
Copenhagen municipality is exposing open data via this site – http://data.kk.dk/ . There are some interesting datasets but also some quite strange ones – for example a dataset containing all the bollards in the city.
Even though the data seems irrelevant at least for me – it is fun to use Power Query to extract the data and then use Power View and Power Map in Excel to visualize the data.
During the process, I learned a nice feature in Power Query that you might find useful as well.
Retrieving the data
The data can retrieved as a CSV file via this link
This data set contains the point for each bollard as a text
I could do a lot of transformation to extract the data but then I noticed that the url contained an parameter called Output format –
And by changing it to JSON – I could retrieve the data as a JSON document.
I then clicked the list of records to expand all records
Converted it into a table
Expanded the Column1
Expanded the Column1.properties to get all meta for the bollards
And then expanded the Column1.geometry to get the point information
This gave me a challenge because the coordinates was a list of values containing the Latitude and longitude and when expanding the column the list values will create two rows per bollard.
The useful Power Query tip
I didn’t want that as I wanted a separate column with longitude and latitude – but this can actually easily be done in Power Query.
Add a Custom Column and add the following formula
Where 1 refers to the row number in the list – Remember its zero based to 1 will be row 2.
And then I have to columns
The rest is renaming columns, deleting unwanted columns and one very important thing specify the Longitude and Latitude columns as decimal numbers otherwise Excel will import them into the datamodel as text values with no option of plotting them in Power View or Power Map.
And then we can start visualizing the data using Power View and/or Power Map.
One import thing about plotting the bollards in Power View is that it doesn’t handle many unique points (locations) – so instead I added the Road Name as location and then Power View will group them on the roadname – then add the Bollard ID in location as well and the user has a drill down option by double clicking the bubble.
In this case I double clicked the Dag Hammerskjölds Alle – with 239 bollards and can see all is placed on the pavement (fortov)
Another options is of course to use Power Map
In this case, I modified some settings in order to view the point better.
You can download the file from here – http://1drv.ms/1opZHgq