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.

41 responses

  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. Pingback: Specifying JSON Query in Power Query – Example Statistics Sweden | Erik Svensen

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

  5. Pingback: Drawing Lines On Maps With Power Map And Power Query | Chris Webb's BI Blog

  6. Pingback: Drawing Lines On Maps With Power Map And Power Query - SQL Server - SQL Server - Toad World

  7. Pingback: Using the WebService() function in Excel 2013 | Chris Webb's BI Blog

  8. Pingback: Query SAP Business ByDesign Web Services via Power Query – Ivan Bond's blog

  9. 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…

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


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

  12. Pingback: Pushing Data From Excel To Power BI Using Streaming Datasets – Chris Webb's BI Blog

  13. Pingback: Calling Microsoft Flow From Power Query And Power BI – Chris Webb's BI Blog

  14. Hi Chris,

    Im trying to make a Post request in Power BI. First, is this possible in the advanced editor? secondly this is the url which i need to make the Post request on.
    http://SERVER:5896/api/v1/Cubes('Cost Allocation’)/Views(‘Cost Allocation’)/tm1.Execute?$expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells

  15. Pingback: Using the Face API with Power BI | Ambiguity vs Information

  16. This method worked very well for me, however i am running into problems getting the dataset to refresh after publishing due to the Web.Contents error. I see from one of your other blog posts how to get around that error for REST GET requests, but how do i get around it for POST requests? I set up the datasource in the gateway and set it to skip the connection test, however that did not help, i still get the error:
    You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh:
    Data source for “SCCR – Names”

    Discover Data Sources
    Query contains unsupported function. Function name: Web.Contents

    Any help would be greatly appreciated, thanks

  17. Pingback: Chris Webb's BI Blog: Pushing Data From Excel To Power BI Using Streaming Datasets Chris Webb's BI Blog

  18. Hi Chris! I use a custom query to access API using Web.Contents. As I have to send a POST request to get session_id (and to use it after in the same query) I can only use Anonymous option. Everything works fine in PBI Desktop, I retrieve data, refresh it, all is fine. When I publish it to PBI Service I can’t refresh it. I get this error: “Processing error Information is needed in order to combine data”. Any ideas about the reason?

      • Thank you, I’m going to dig in this direction. Your blog is really priceless, I’ve already learnt a lot here. God bless you!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: