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