Chris Webb's BI Blog

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

Web Services And POST Requests In Power Query

with 16 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:

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(

                    "" & 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
  • The important step is GetElevations. The code is:


    "" & 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.

Written by Chris Webb

April 19, 2014 at 9:00 am

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


        David Hager

        April 21, 2014 at 8:08 pm

      • Thanks for the excellent post. The authentication is indeed an issue. I need to connect to a service within our internal Domain with Windows authentication and get the message “[DataSource.Error] Web.Contents with the Content option is only supported when connecting anonymously.” Any ideas on how to solve it ? I cannot switch to Anonymous authentication.


        October 28, 2014 at 2:54 pm

      • Sorry Rahul, I don’t know. You should ask this question on the Power Query MSDN forum.

        Chris Webb

        October 29, 2014 at 9:42 pm

    • Rahul

      November 13, 2014 at 3:57 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.



    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:

    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?



    July 23, 2014 at 12:40 pm

  4. […] – but I found the inspiration/solution via Chris Webb’s blogpost – if I could specify the JSON Query as the Content it should be […]

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


    November 2, 2014 at 7:56 pm

    • I don’t think you can convert a record directly to JSON – you would need to take the data from the record and construct the JSON from that data manually using some M code.

      Chris Webb

      November 2, 2014 at 8:05 pm

      • If at least, I had a function like string.format, it would be very helpful


        November 3, 2014 at 2:58 am

Leave a Reply

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

You are commenting using your 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


Get every new post delivered to your Inbox.

Join 3,962 other followers

%d bloggers like this: