Forum Discussion
Retrieve API response headers
I have an API query from Power BI Desktop that looks like this:
let
authKey = "Basic " & Binary.ToText(Text.ToBinary("UserID" & ":" & "PassWD"),0),
url="https://mywebapi.com",
Source = Json.Document(Web.Contents(url, [Headers=[Authorization=authKey], RelativePath="/Connection"]))
in
Source
A successful logon request returns an HTTP status code of 200 and the TOken is returned in the TOken response header.
How can I access the information in the response headers? Thank you.
Hi,
I understand that they are methods to obtain the request headers, not response headers.
In this article:
explains this:
"[...] but even with those articles and videos at hand + the full documentation of the Web.Contents function here, you might not get some queries to work because of how Power Query’s refresh operation was designed to work on the Power BI Service and how some other features are limited like not being able to see the response header of your Web.Contents request when using the Value.Metadata function"
So, the conclusion I get is that the only way to get all the information from the responseheaders is to create a Custom Connector.
"[...] Request Limit, Throttling, Token expiration and response Headers– a big restriction with Power Query is that, by design, you are unable to see the headers of the response of your requests. You only get the body of the response, but the Header contains important information, like the next page url, total elements in the endpoint and more. Now with the Extensibility Model you are able to see exactly what the response Header provides and use the values to paginate or just create a query plan for your connector so you never get throttled."
Thank you all.
11 Replies
I'm looking to do the same thing. I'm calling a REST API through Web.Contents and would like to parse the response headers. In Postman, I've identified response headers I'd like to expose:
Back in Power Query, Value.Metadata on the response exposes the value for the "Content-Type" header, but nothing else.
Web searches yield varying answers from "you can't do that" to "use Python to read your IE cookies, start a local web server, and use Power Query to fetch values from the local server".
I feel there should be a M function that should be able to expose all response headers. Am I wrong?
Try WebMethod.head() or Odata.Feed(), If still can't retrieve the header of http request, I recommend you to use R script to get the http headers instead.
https://msdn.microsoft.com/en-us/query-bi/m/webmethod-head.
https://msdn.microsoft.com/en-us/query-bi/m/odata-feed.
Regards,
Jimmy Tao
Hi,
I understand that they are methods to obtain the request headers, not response headers.
In this article:
explains this:
"[...] but even with those articles and videos at hand + the full documentation of the Web.Contents function here, you might not get some queries to work because of how Power Query’s refresh operation was designed to work on the Power BI Service and how some other features are limited like not being able to see the response header of your Web.Contents request when using the Value.Metadata function"
So, the conclusion I get is that the only way to get all the information from the responseheaders is to create a Custom Connector.
"[...] Request Limit, Throttling, Token expiration and response Headers– a big restriction with Power Query is that, by design, you are unable to see the headers of the response of your requests. You only get the body of the response, but the Header contains important information, like the next page url, total elements in the endpoint and more. Now with the Extensibility Model you are able to see exactly what the response Header provides and use the values to paginate or just create a query plan for your connector so you never get throttled."
Thank you all.
Does python scripting work for this in your context?
Here is an example with an API I use.
import requests
base_url = "https://directory.spineservices.nhs.uk/ORD/2-0-0/organisations?&Limit=1000&Status=Active"
response = requests.get(base_url)
headers = response.headers
print(headers)
