Web Services And POST Requests In Power Query

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.

16 thoughts on “Web Services And POST Requests In Power Query

  1. 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

  2. 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

  3. Hi Chris,
    Is it possible to convert a record to binary or to Json and post it in Web.Contents ?
    I try to do the following
    request=[message=”Hello”, params = [Start= #date(2014,9,1), End=#date(2014,10,16)]],
    I don’t know how to post it as binary
    or at least to convert it in Json and then use the Text.ToBinary(request)

    GetCallsPerDay = Web.Contents(

    “http://localhost:21092//GetCallsPerDay”,

    [Content=request]),

    thank you
    Stefan

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