Plotting speed limits in Copenhagen using public data, Power Query and Power Map

My hometown Copenhagen is sharing a lot of public data via – – and one of the latest is a dataset with speed limits for the different roads in Copenhagen – and the dataset is shared as a geojson resource –

So why not try to see how the Power BI tools in Excel 2013 could visualize this information.

First – Power Query


Copy the geojson url –

And activate Power Query and choose to import from Web

Power Query will then show you the information about the json document

And then navigate to the list – features


Then convert the list to a table and expand the different elements in the document


In order to get the Latitude and Longitude we need to extract the values from the geometry.coordinates field – by adding two new calculated columns – it can be done like this

Then I do some renaming of columns and remove unwanted columns I end up with a relative simple dataset


The full query is listed here


Source = Json.Document(Web.Contents(“”)),

features = Source[features],

#”Table from List” = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expand Column1″ = Table.ExpandRecordColumn(#”Table from List”, “Column1”, {“type”, “id”, “geometry”, “geometry_name”, “properties”}, {“Column1.type”, “”, “Column1.geometry”, “Column1.geometry_name”, “”}),

#”Expand Column1.geometry” = Table.ExpandRecordColumn(#”Expand Column1″, “Column1.geometry”, {“type”, “coordinates”}, {“Column1.geometry.type”, “Column1.geometry.coordinates”}),

#”Expand Column1.geometry.coordinates” = Table.ExpandListColumn(#”Expand Column1.geometry”, “Column1.geometry.coordinates”),

#”Expand” = Table.ExpandRecordColumn(#”Expand Column1.geometry.coordinates”, “”, {“id”, “bydel”, “vejid”, “vejnavn”, “frastation”, “tilstation”, “hastighedsgraense”, “anb_hastighedsgraense”}, {“”, “”, “”, “”, “”, “”, “”, “”}),

#”Added Custom” = Table.AddColumn(#”Expand”, “Custom”, each [Column1.geometry.coordinates]{1}),

#”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Custom.1”, each [Column1.geometry.coordinates]{0}),

#”Renamed Columns” = Table.RenameColumns(#”Added Custom1″,{{“Custom”, “Latitude”}, {“Custom.1”, “Longitude”}}),

#”Removed Columns” = Table.RemoveColumns(#”Renamed Columns”,{“”, “Column1.type”, “”, “Column1.geometry.type”, “Column1.geometry.coordinates”, “Column1.geometry_name”, “”, “”, “”, “”}),

#”Changed Type” = Table.TransformColumnTypes(#”Removed Columns”,{{“”, Int64.Type}, {“Latitude”, type number}, {“Longitude”, type number}}),

#”Renamed Columns1″ = Table.RenameColumns(#”Changed Type”,{{“”, “SpeedLimit”}, {“”, “Roadname”}, {“”, “CityArea”}})


#”Renamed Columns1″


I return the data to the datamodel and we are ready to activate Power Map.


And by using the filter function I can find the roads where the limit is 60 km/h or higher


You can download a copy of the file – here


Leave a Reply

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

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

Facebook photo

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

Connecting to %s