Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

A lot of public datasets is available via this site http://datahub.virk.dk/data/search – currently only in Danish.

One of the datasets contains information about the location of all the antennas in Denmark – http://mastedatabasen.dk/Viskort/ContentPages/DataFraDatabasen.aspx?callingapp=mastedb – and it has an excellent API that enables you to extract data about the antennas.

Here is how you can extract the data using Power Query and then visualize it in Power Map using Excel 2016 – you can do the same in Excel 2013 but I was playing around with Excel 2016.

First I select the

And paste in this query – which will return all the antennas in Denmark – it will return about 27.000 but I set the Max to 100.000

http://mastedatabasen.dk/Master/antenner.json?maxantal=100000

Click the To Table in the ribbon – accept the default and then Expand the column – then you have the data

The rest of the steps is deleting unwanted columns and renaming the fields and changing the established column to a date.

All the steps is as follows

let

Source = Json.Document(Web.Contents(“http://mastedatabasen.dk/Master/antenner.json?maxantal=100000”)),

#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“vejnavn”, “husnr”, “postnummer”, “kommune”, “idriftsaettelsesdato”, “forventet_idriftsaettelsesdato”, “etrs89koordinat”, “wgs84koordinat”, “tjenesteart”, “teknologi”, “unik_station_navn”, “radius_i_meter”, “frekvensbaand”}, {“vejnavn”, “husnr”, “postnummer”, “kommune”, “idriftsaettelsesdato”, “forventet_idriftsaettelsesdato”, “etrs89koordinat”, “wgs84koordinat”, “tjenesteart”, “teknologi”, “unik_station_navn”, “radius_i_meter”, “frekvensbaand”}),

#”Removed Columns” = Table.RemoveColumns(#”Expanded Column1″,{“etrs89koordinat”}),

#”Expanded wgs84koordinat” = Table.ExpandRecordColumn(#”Removed Columns”, “wgs84koordinat”, {“bredde”, “laengde”}, {“wgs84koordinat.bredde”, “wgs84koordinat.laengde”}),

#”Expanded tjenesteart” = Table.ExpandRecordColumn(#”Expanded wgs84koordinat”, “tjenesteart”, {“navn”}, {“tjenesteart.navn”}),

#”Expanded teknologi” = Table.ExpandRecordColumn(#”Expanded tjenesteart”, “teknologi”, {“id”, “navn”}, {“id”, “navn”}),

#”Renamed Columns” = Table.RenameColumns(#”Expanded teknologi”,{{“wgs84koordinat.bredde”, “Latitude”}, {“wgs84koordinat.laengde”, “Longitude”}}),

#”Removed Columns1″ = Table.RemoveColumns(#”Renamed Columns”,{“forventet_idriftsaettelsesdato”, “kommune”}),

#”Expanded postnummer” = Table.ExpandRecordColumn(#”Removed Columns1″, “postnummer”, {“nr”, “navn”}, {“nr”, “navn.1”}),

#”Renamed Columns1″ = Table.RenameColumns(#”Expanded postnummer”,{{“nr”, “ZipCode”}, {“navn.1”, “City”}, {“tjenesteart.navn”, “Servicename”}, {“navn”, “Technology”}}),

#”Removed Columns2″ = Table.RemoveColumns(#”Renamed Columns1″,{“unik_station_navn”}),

#”Changed Type” = Table.TransformColumnTypes(#”Removed Columns2″,{{“idriftsaettelsesdato”, type date}}),

#”Renamed Columns2″ = Table.RenameColumns(#”Changed Type”,{{“idriftsaettelsesdato”, “Established”}})

in

#”Renamed Columns2″

Then click close and load to – and select to “Only Create Connection” and load the data to the data model in the workbook

Then we can start to visualize the with Power Map

I like the new layer pane in Power Map – it makes is easy to modify the layer – instead of the “old” steps in Power Map in Excel 2013.

In this example I have chosen to plot the antennas by latitude, added the Established as a time and added a filter for LTE – the 4G network.

And now I can click the time line to see the development over time.

I can also see why I need an internet connection in my summerhouse –

Not a lot of antennas nearby – 😦

You can download the file here to

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

My hometown Copenhagen is sharing a lot of public data via – http://data.kk.dk/ – 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 – http://data.kk.dk/dataset/trafikhastigheder

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 – http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:parkeringstaelling_i_zoner&outputFormat=json&SRSNAME=EPSG:4326

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

let

Source = Json.Document(Web.Contents(“http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:trafikhastigheder&outputFormat=json&SRSNAME=EPSG:4326”)),

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.id”, “Column1.geometry”, “Column1.geometry_name”, “Column1.properties”}),

#”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 Column1.properties” = Table.ExpandRecordColumn(#”Expand Column1.geometry.coordinates”, “Column1.properties”, {“id”, “bydel”, “vejid”, “vejnavn”, “frastation”, “tilstation”, “hastighedsgraense”, “anb_hastighedsgraense”}, {“Column1.properties.id”, “Column1.properties.bydel”, “Column1.properties.vejid”, “Column1.properties.vejnavn”, “Column1.properties.frastation”, “Column1.properties.tilstation”, “Column1.properties.hastighedsgraense”, “Column1.properties.anb_hastighedsgraense”}),

#”Added Custom” = Table.AddColumn(#”Expand Column1.properties”, “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.properties.anb_hastighedsgraense”, “Column1.type”, “Column1.id”, “Column1.geometry.type”, “Column1.geometry.coordinates”, “Column1.geometry_name”, “Column1.properties.id”, “Column1.properties.vejid”, “Column1.properties.frastation”, “Column1.properties.tilstation”}),

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

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

in

#”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

 

Specifying JSON Query in Power Query – Example Statistics Sweden

Not all data API provides you with the same help as Statistics Denmark (see a previous blog post link) where you get help to construct the URL to retrieve the data via a GET Request (http://api.statbank.dk/console#data)

In another project, I was interested in retrieving data about Sweden – and quickly found Statistics Sweden – http://www.scb.se/en_/. The site has a lot of interesting data and facts about Sweden and they also provide access the the data via and API (http://www.scb.se/en_/About-us/Open-data-API/)

Unfortunately the Swedes doesn’t provide the same help as their Danish colleagues, so we have to use a little Power Query magic to retrieve the data – J

Here is how its done.

 

If you click the example link to retrieve the population for 2011 and 2012 – http://api.scb.se/OV0104/v1/doris/en/ssd/BE/BE0101/BE0101A/BefolkningNy – you will get a JSON response but this will only contain the variables that can be used to query the result and no data.

So I started to search the documentation and found that in order to get the data you have to create a GET request to the API with a JSON Query to get data.

But how can we do this in Power Query.

The Help on Web.Contents() in Power Query doesn’t provide much help besides the remark about POST/GET under the content option field (http://office.microsoft.com/en-us/excel-help/web-contents-HA104112310.aspx?CTT=5&origin=HA104122363 – but I found the inspiration/solution via Chris Webb’s blogpost http://cwebbbi.wordpress.com/2014/04/19/web-services-and-post-requests-in-power-query/ – if I could specify the JSON Query as the Content it should be possible.

So lets try

First on the PowerQuery tab select “From Web” and insert the address – http://api.scb.se/OV0104/v1/doris/en/ssd

 

This will give you a list of all the different variables you can set via the API, but not the data

In order to specify the JSON Query we have to switch to the advanced editor and modify the PowerQuery.

First we need a variable that holds the JSON Query – but remember to replace the ” with “” as the text in the query has to be stated properly.

Then we can use the content variable in the call to the Web.Contents –by adding “, [Content=Text.ToBinary(content)]))” to call.

When we click ok – we get the data J

Then we navigate the JSON result by expanding the list, convert to a table and expand the different columns in the query.

 

The result is now a bunch of keys for the years and regions in Sweden. We could then create other queries to the API in order to get these key values but there is actually an easier way. We can ask the API to return the data returned by JSON query as CSV instead of JSON, and remember to modify the Source to only web.contents and not a Json.Document.

Example to retrieve the population for Sweden in 2013 split by region and ages

 

let

PostContents= “{

“”query””: [

{

“”code””: “”Kon””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”1″”,

“”2″”

]

}

},

{

“”code””: “”Alder””,

“”selection””: {

“”filter””: “”all””,

“”values””: [

“”*””

]

}

},

{

“”code””: “”ContentsCode””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”BE0101N1″”

]

}

},

{

“”code””: “”Tid””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”2013″”

]

}

}

],

“”response””: {

“”format””: “”csv””

}

}

“,

Source = Web.Contents(“http://api.scb.se/OV0104/v1/doris/sv/ssd/START/BE/BE0101/BE0101A/BefolkningNy”,[Content=Text.ToBinary(PostContents)]),

#”Imported CSV” = Csv.Document(Source,null,”,”,null,1252),

#”Removed Top Rows” = Table.Skip(#”Imported CSV”,1),

#”Renamed Columns” = Table.RenameColumns(#”Removed Top Rows”,{{“Column1”, “RegionKey”}, {“Column2”, “Age”}, {“Column3”, “Gender”}, {“Column4”, “Population”}}),

#”Split Column by Delimiter” = Table.SplitColumn(#”Renamed Columns”,”RegionKey”,Splitter.SplitTextByEachDelimiter({” “}, null, false),{“RegionKey.1”, “RegionKey.2”}),

#”Inserted Text Length” = Table.AddColumn(#”Split Column by Delimiter”, “Length”, each Text.Length([RegionKey.1]), type number),

#”Filtered Rows” = Table.SelectRows(#”Inserted Text Length”, each ([Length] = 4)),

#”Renamed Columns1″ = Table.RenameColumns(#”Filtered Rows”,{{“RegionKey.1”, “RegionKey”}, {“RegionKey.2”, “RegionName”}}),

#”Replaced Value” = Table.ReplaceValue(#”Renamed Columns1″,” år”,””,Replacer.ReplaceText,{“Age”}),

#”Replaced Value1″ = Table.ReplaceValue(#”Replaced Value”,”+”,””,Replacer.ReplaceText,{“Age”}),

#”Filtered Rows1″ = Table.SelectRows(#”Replaced Value1″, each ([Age] <> “totalt ålder”)),

#”Replaced Value2″ = Table.ReplaceValue(#”Filtered Rows1″,” “,””,Replacer.ReplaceText,{“Population”}),

#”Changed Type” = Table.TransformColumnTypes(#”Replaced Value2″,{{“Population”, Int64.Type}, {“Age”, Int64.Type}}),

#”Inserted Custom” = Table.AddColumn(#”Changed Type”, “EndOfYear”, each Date.From(“2013/12/31”)),

#”Removed Columns” = Table.RemoveColumns(#”Inserted Custom”,{“Length”}),

#”Filtered Rows2″ = Table.SelectRows(#”Removed Columns”, each ([Population] <> 0)),

#”Changed Type1″ = Table.TransformColumnTypes(#”Filtered Rows2″,{{“EndOfYear”, type date}})

in

#”Changed Type1″

 

This result can be returned to the datamodel

And we can start using PowerView, PowerMap to visualize the population.

OBS – As the API returns data with Swedish number format – you have to change the Power Query local to Swedish (Sweden) in order for the Query not to fail.

 

And then we can start analyzing the data in a pivottable

 

Or in PowerView in Excel

 

Here is the scary scenario for men when turning 70 …. Looks very dangerous. And Power Map lets us plot by county easily.

 

Or use the new PowerMap filter function to create a heatmap of the regions with 0-3 year old

 

If you sell diapers or sell children’s shoes in Sweden, you can see where to focus.

You can download a copy of the workbook – from here, and let me know if you have questions or like it.

 

 

 

The Icelandic volcano Vatnajökull activity analyzed in Excel Power Map

The Vatnajökull volcano is erupting these hours and I thought it would be interesting to see if I could find some data about the activity – and sure enough – http://en.vedur.is/earthquakes-and-volcanism/earthquakes/vatnajokull/ has a table with the last 48 hours of earthquakes near the volcano.

So by using Power Query and then Power Map in Excel – I was able to make this video of the activity in under 15 minutes.

 

You can download a copy of the workbook here – the data is refreshable so you update it to see the latest 48 hours.

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

 

 

 

Using Power BI to enrich your data using public datasets – part 1

Yesterday I presented at a LinkedIn group – Microsoft BI Professionals Denmark a session about how you can use Power Query to enrich your datamodels using public datasets.

My demo contained examples on how to use the API from Statistics Denmark link – to extract all sorts of information about Denmark directly into your datamodel and how you can use the public dataservice http://geo.oiorest.dk/ to do a reverse look up of Danish regions, municipalities from a specific lat/long coordinate.

You can download my demo files from here.

The presentation – Link

The Excel file – Link

I will create a Part 2 of this post where I will go through the steps of getting the data from Statistics Denmark into your model.

Using Power Query and Power Map to visualize public data of the windmills in denmark

While preparing a training session for a customer within the Windmill industry I found public data that lists all the windmills in Denmark with their size and location.

The data can be found here – http://www.ens.dk/info/tal-kort/statistik-noegletal/oversigt-energisektoren/stamdataregister-vindmoller

So I decided to see what Power BI could do with this dataset.

The data

The file with the latest data (http://www.ens.dk/sites/ens.dk/files/byggeri/anlaegprodtilnettet.xls is named the same way and is structured in a way that makes it easy to make custom analysis and visualisations.

So first tool to use in the Power BI stack…

Power Query to the rescue

First – switch to the Power Query tab and choose to load data from File and Excel File and paste the address to the file in the file open – it does take some time but it will work.

And Power Query will open and list the tables that exists in the file

The data we are interested in starts at row 18 and contains a lot of columns that we aren’t interested in.

And there is also some formatting of columns from text to numbers etc.

Here is the total list of steps shown

Create a Power Query function to create Latitude and Longitude

The data file has doesn’t contain Latitude or longitude but the coordinates is listed with the “European Terrestrial Reference System” – so I had to find a way to convert the values into Latitude and longitude.

The Danish Geodata Agency provides a free webservice that can do the conversion for us – http://geo.oiorest.dk/documentation/api/koordinat.aspx – examples.

So for instance the first wind mill is located at

http://geo.oiorest.dk/wgs84.html?etrs89=6171175,720898.4

And inspired by Rafael Salas – blog post – http://www.rafael-salas.com/2013/08/power-query-creating-function-to.html – I decided to create a Power Query function to do the calculation.

So I created a blank Query and added the following query

The function takes two arguments the east and north coordinates, and uses those coordinates to get and XML table from the Web service. As I run with a comma as decimal separator on due to my Danish regional settings I have to replace the comma with a dot as the web service requires the decimal separator to be a dot.

The result is loaded into a table with the “bredde” – latitude and “Længde” – longitude

And the function returns both of these columns.

Then I can use that function in my first query by adding a Custom Column.

And expand that to get both latitude and longitude as columns in my query.

And voila the custom calculated columns.

Then some number formatting of some columns and renaming to English heading and we are ready to send the data to Excel

The data back in Excel

Returning the query to Excel will then give me the list of all 5.126 running windmills in Denmark per September 2013.

Pretty awesome – it does take about 2-3 minutes for it to update/lookup all the geo location – but only one isn’t matched and that is due to an empty row.

Using Power Map to visualize the data

With the data nicely washed and geotagged we can use Power Map to visualize the data.

Power Map’s first guess on the geomapping is actually very good. The “Kommune” is set to county – which is correct and because I named the columns Latitude and Longitude these are automatically also linked correct.

With the Geography properly matched we can move “Next” to visualize the data.

So for instance the KWH by Supplier

Or the KWH by Region/County

Or the world biggest sea wind mill park

Or a heatmap

This is so much fun and finally let make a video of the development over time.

Power map video

Power map also enables us to create a timeline and play the timeline. The data has the date of when the windmill was established so we can use that date to visualize how the windmills have evolved over time.

So by adding the established field to the Timeline – power map can visualize the development over time.

This can be created as a video.

Creating the 45 sec video at the medium format took about 10 minutes to create – so be patient.

Here is a link to the video – http://sdrv.ms/17cBIrx

The file

You can download the example file – here

Comments please

You are more than welcome to add a comment whether you find this cool or not J