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.
Tak for et rigtig godt indlæg i går! Cool at se de mere avancerede måder at konvertere data fra danske datakilder!
Hi
Glad you liked my session
Thx
Hentet din Excel-fil, og fik en fejl i ‘Kommune’-kollonen. Jeg tilføjet en ‘/’ forran kommune i FILTERXML kommandoen, og det hjalp:
=FILTERXML(WEBSERVICE(Start&SUBSTITUTE([@Lat];”,”;”.”)&”,”&SUBSTITUTE([@Long];”,”;”.”));”//kommune/navn”)
(oprindelig var der “/kommune/navn”)
Hi,
I can make it work both with one and two // – but I do experience #Value error sometimes but an autofilter on those values – makes it look up again and the problem is solved.
Erik