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
ChangedTypeIt’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:
You can download the sample workbook here.
Getting this error:
Web.Contents with the Content option is only supported when connecting anonymously.
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.
But I believe that I did not handle that process correctly when first presented with it. Any way to fix it?
Try clicking on Data Source Settings in the Power Query tab in the ribbon, then editing the settings for the web service there.
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.
Thanks!
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.
Sorry Rahul, I don’t know. You should ask this question on the Power Query MSDN forum.
Thanks Chris. To close the loop on this, I posted the question on MSDN and here is a link to the response from :
https://social.technet.microsoft.com/Forums/en-US/6366dd5d-302c-42fb-8283-c833954c3858/sending-contents-to-windows-authenticated-service-results-in-an-error-content-option-is-only?forum=powerquery
Hi. I know this is an old discussion, but I came across it after running into a similar problem. Thanks for confirming that what I want to do isn’t possible!
I’ve been trying to use an Excel Power Query to get data from an API that uses Basic authentication and requires a POST request. I keep getting the same error message described above. Even if I try to use the Anonymous option, I still get the same error, rather than an authentication failure. I guess PQ can tell that the API uses Basic authentication, even if I tell it otherwise.
Instead of using PQ, I’ve had to use WinHTTP from VBA.
For future reference, Power Query custom data connectors do support making authenticated POST requests, so when Excel supports custom data connectors then this will be another option.
Thanks for that, Chris. I hadn’t even heard of custom data connectors before, and they sound a bit too advanced for me. I’ve only just started learning about Power Query.
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
No, not at the moment. Lots of people have asked for this though.
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
Hmm, not sure – I’d have to do some experiments.
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
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.
If at least, I had a function like string.format, it would be very helpful
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
Hi Martin, the code looks ok to me. You might have to use a tool like Fiddler to see what Power Query is actually sending to the web service?
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…
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
I guess this is referring to the way you are authenticating. Are you using the anonymous option for this URL?
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.
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
Yes, it’s possible in Power BI in the same way as I show here in Power Query.
Hi chris, you might want to use http://hilite.me for adding your code to blogs, it’s easier to read.
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
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?
Sounds like you need to set data privacy levels on your data sources? I have lots of blog posts on this subject.
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!
Hello! I want to add a body to this code:
let
url = “https://pos.linisco.com.ar/sale_orders”,
Source = Json.Document(Web.Contents(url, [Headers = [#”Content-Type”=”application/json”, #”Accept”=”application/json”, #”X-User-Email”=”xxxxxx”, #”X-User-Token”=”xxxxxx”]])),
in
Source
The body in postman is:
{
“fromDate” : “1/1/2022”,
“toDate” : “12/11/2022”
}
Any suggestions?
Thanks!
Hello! I want to add a body to this code:
let
url = “https://pos.linisco.com.ar/sale_orders”,
Source = Json.Document(Web.Contents(url, [Headers = [#”Content-Type”=”application/json”, #”Accept”=”application/json”, #”X-User-Email”=”xxxxxx”, #”X-User-Token”=”xxxxxx”]])),
in
Source
The body in postman is:
{
“fromDate” : “1/1/2022”,
“toDate” : “12/11/2022”
}
Any suggestions?
Thanks!