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:


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:


    //Insert your Bing Maps API key here

    BingMapsKey = "xxxx",


    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,


    //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}})




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:


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


    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:


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.


  2. Hi Chris,
    I’m trying to use Power Query Web.Contents() to repeatedly query this page which contains a POST form:

    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?


  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(



    thank you

  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:

    Bron = Xml.Tables(Web.Contents(“http://removed.asmx?wsdl”,


    <![CDATA[ 20161100000 ]]>



    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.


      • 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


  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