Skip to content
August 11, 2014 / Erik Svensen

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

http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:pullert&outputFormat=csv&SRSNAME=EPSG:4326

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 –

http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:pullert&outputFormat=json&SRSNAME=EPSG:4326

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

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: