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.

27 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

  4. Hello Chris,

    I’m trying to get Power Query to grab data from our ERP system using a web service based on your examples. I think I’m getting close but I keep getting a ‘DataSource.Error’ saying Web.Contents can’t retrieve the data from the URL with the error code ‘(415): Unsupported Media Type’. Maybe there’s something simple I’m missing? Here’s the code:

    let
    Bron = Xml.Tables(Web.Contents(“http://removed.asmx?wsdl”,
    [Content=Text.ToBinary(

    removed
    removed
    removed
    removed

    <![CDATA[ 20161100000 ]]>


    )
    ]))

    in
    Bron

    The XML is based on an example from the developer of the ERP system. Maybe you can have a look at the code and see where things might go wrong?

    Thanks in advance.

    Martin

      • Hello Chris, many thanks for having a look and the advice. I’ve tried Fiddler but I can’t detect what’s going wrong, it looks like what’s getting sent is ok. When I test the XML code which is in the Text.ToBinary() function in SOAPUI it works perfectly; it returns XML with the data I’ve requested. This is so frustrating, I hope Power Query will get an easier way to connect to a Web Service in the future. I was hoping I could replace the VBA routine I’m using now (generated with the good old MS Web Services Toolkit) with Power Query but I can’t get it to work…

  5. Hi
    I am trying to get data from Web API with POST method and I am getting such an error:
    Web.Contents with the Content option is only supported when connecting anonymously. Do you have any idea how to bypass this?

    Thank you

    Borut

  6. I have no idea if this is possible but.. could you use a Post request to add a new item to a SharePoint 2013 list :)

    What about Put/Patch and Merge?

    • You can only use GET and POST I’m afraid. What’s more, because there is no guarantee that Power Query will execute your code only once when a query runs, if you could write code to add a new item there’s a chance that the same value could be added multiple times.

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