Using web services from Power BI

[rdoggart]

I would like to know is it possible in Power BI to add a new column to a table that would be populated via a call to a web service.

I have a table with easting and northing map data that I need to convert to latitude and longitude and store the values so I can plot them on the map.

I have found this web service available on the internet that does what I want but would like to know is it possible to call it from Power BI:

http://beta.ngs.noaa.gov/gtkws/geo?northing=6967871.966&easting=495059.365&zone=56&hemi=S

Returns:

{ "datum": "nad83", "lat": -27.41252065, "lon": 152.950019168, "height": 0.0, "utm": { "northing": 6967871.966, "easting": 495059.365, "zoneId": "56", "convergence": "-00 01 22.839132", "scaleFactor": 0.9996003 } }

[chrisu]

Yes, this is possible.  You'll have to create a parameterized function that takes in your data and calls the web service. Once you have the function you can add it as a custom column.  See the following sites for examples:

http://datachix.com/2014/05/22/power-query-functions-some-scenarios/

http://satalyst.com/power-query-creating-a-parameterized-function-in-m/

[TomMartens · Accepted solution]

Hey,

I would start with this post by Chris Webb

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

unfortunately I'm currently not able to provide a solution.

Nevertheless, hope this helps

Edited somewhat later ...

I created a little table that consists of two columns (datataype = text)

Then I added a Custom Column using this Formula

Web.Contents(
    "http://beta.ngs.noaa.gov/gtkws/geo", 
    [
        Query=
        [
            northing=[northings],
			easting=[eastings],
			zone="56",
			hemi="S"
        ]
    ]
)

Due to the fact that Web.Contents returns a binary I used Transform -> JSON from the context menu of new column.

Finally I expand the record and select the appropriate columns

The steps

Here you go