Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Web Services And POST Requests In Power Query

with 9 comments

In my recent post on web services in Power Query I mentioned that while the Power Query Web.Contents() function generates a GET request by default, you can make it generate a POST request by specifying the Content option. Since this is a useful thing to be able to do I thought I’d put together a detailed example of how this works.

For my example I’m going to use the Bing Maps Elevations API, which allows you to get the elevation in metres for a set of geographic locations. You can read the documentation here:

http://msdn.microsoft.com/en-us/library/jj158961.aspx

As it says at the bottom of the page, if you have a large number of locations to pass to the web service you can do so by passing them as a comma delimited list of latitudes and longitudes using a POST request. Here’s the code for a Power Query query that generates a list of latitudes and longitudes that stretches across the UK from North Wales in the west to the Wash in the east and finds the elevation for each point:

let

    //Insert your Bing Maps API key here

    BingMapsKey = "xxxx",

    //Latitude

    Latitude = 53,

    //Generate a list of longitudes

    Longitudes = List.Numbers(-4.5,66,0.1),

    //Generate a list of latitudes and longitudes

    PointList = List.Transform(Longitudes, 

        each  Number.ToText(Latitude) & "," & Number.ToText(_)),

    //Turn this list to comma delimited text

    PointListText = Text.Combine(PointList,","),

    //Add the text "points=" to the beginning

    PostContents = "points=" & PointListText,

    //Call the Elevations web service

    GetElevations = Web.Contents(

                    "http://dev.virtualearth.net/REST/v1/Elevation/List?key=" & BingMapsKey,

                    [Content=Text.ToBinary(PostContents)]),

    //Treat the result as a JSON document

    ImportedJSON = Json.Document(GetElevations),

    //Navigate to the elevations data

    resourceSets = ImportedJSON[resourceSets],

    resourceSets1 = resourceSets{0},

    resources = resourceSets1[resources],

    resources1 = resources{0},

    elevations = resources1[elevations],

    //Turn the elevations data into a table

    TableFromList = Table.FromList(elevations, Splitter.SplitByNothing(), 

                                null, null, ExtraValues.Error),

    //Rename the column containing the elevations

    RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Elevations"}}),

    //Add a column containing the latitude

    InsertedCustom = Table.AddColumn(RenamedColumns, "Latitude", each Latitude),

    //Add an index column

    InsertedIndex = Table.AddIndexColumn(InsertedCustom,"Index"),

    //Use the index column to find the longitude for the current row

    InsertedCustom1 = Table.AddColumn(InsertedIndex, "Longitude", each Longitudes{[Index]}),

    //Remove the index column

    RemovedColumns = Table.RemoveColumns(InsertedCustom1,{"Index"}),

    //Set all columns to data type number

    ChangedType = Table.TransformColumnTypes(RemovedColumns,

                    {{"Elevations", type number}, {"Latitude", type number}, 

                    {"Longitude", type number}})

in

    ChangedType

 

It’s quite easy to edit the code so that it generates a list of latitudes and longitudes across the country of your choice…

Two things to point out:

  • To get this to work you need to insert a Bing Maps API key in the first step where indicated. If you don’t have one, you can get your own at https://www.bingmapsportal.com
  • The important step is GetElevations. The code is:

    Web.Contents(

    "http://dev.virtualearth.net/REST/v1/Elevation/List?key=" & BingMapsKey,

    [Content=Text.ToBinary(PostContents)]),

    You can see here how the list of locations is passed to the Web.Contents() function (documentation here) via the Content field; notice also that I’ve had to use Text.ToBinary() on the text that I’m passing in.

Here’s the output in Power Map:

image

You can download the sample workbook here.

Written by Chris Webb

April 19, 2014 at 9:00 am

9 Responses

Subscribe to comments with RSS.

  1. Getting this error:

    Web.Contents with the Content option is only supported when connecting anonymously.

    David Hager

    April 19, 2014 at 6:12 pm

    • That doesn’t sound good – I was able to connect anonymously to the Bing Maps web service because it allows you to pass the key via the URL.

      Chris Webb

      April 19, 2014 at 7:13 pm

      • But I believe that I did not handle that process correctly when first presented with it. Any way to fix it?

        David Hager

        April 19, 2014 at 9:53 pm

      • Try clicking on Data Source Settings in the Power Query tab in the ribbon, then editing the settings for the web service there.

        Chris Webb

        April 21, 2014 at 5:12 pm

      • It worked! It’s easy, when you know what to do. I am probably typical of most Excel types. Know lots of arcane stuff, but the obvious alludes me.

        Thanks!

        David Hager

        April 21, 2014 at 8:08 pm

  2. Hello Chris:

    Can we add filtering of data on fly (like a pivot) in Power Map?
    e.g. I am displaying data of all the states and now I want to see data for few states only like Washington, Texas only.

    Thanks,
    Saurabh

    Saurabh

    April 25, 2014 at 7:45 am

    • No, not at the moment. Lots of people have asked for this though.

      Chris Webb

      April 25, 2014 at 8:07 am

  3. Hi Chris,
    I’m trying to use Power Query Web.Contents() to repeatedly query this page which contains a POST form:
    https://www.asb.co.nz/investserv/tracker/latest_unit_prices.asp

    The page contains a POST form where normally the user selects a date then clicks “Go”. I’m not sure if Web.Contents() with the Content option can even be used in this situation, as I haven’t been able to get it working so far.

    Any idea if this is possible?

    Owen

    ozeroth

    July 23, 2014 at 12:40 pm


Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,081 other followers

%d bloggers like this: