Retrieve API response headers

[lauritxi]

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.

[TomTomTom]

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)  

[tonmcg]

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?

[v-yuta-msft]

Hi lauritxi and tonmcg,

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

[Anonymous]

Hi, 

So there exists a workaround to get the response header? 

Can you give a bit more precise information, please?

regards,

Axel Vulsteke

[gabrielsroka]

Hi @Anonymous,

You have to write a custom Data Connector. See https://github.com/Microsoft/DataConnectors

[tonmcg]

To round out this thread, I've confirmed the only way to retreive the response header for a Socrata API endpoint in Power BI is to build a custom connector.

Here's a screenshot of a custom connector I built to the City of Seattle Fire Emergency Calls endpoint. I use the Value.Metadata Power Query M function ask for the response headers:

Here's what the results look like after I test this query in Visual Studio:

Note: I'm not using the OData endpoint but rather, the regular endpoint for this Http request to the Socrata API.

[Raikoh]

Hi there,

I was wondering if this was changed. Currently I am developing a custom connector but right now I can only access a shortened versions of the response headers 😞

Thanks in advance for any help

[Anonymous]

Hi,

How to implement with paging concept , Using limit fetch the record until header response is null epeat the cycle until the Link header is no longer present in the response, which indicates you are on the last page. Any idea.

[michaelshparber]

Why custom connector is able to get the response header?

[TopherH]

Thanks!

I feel that this should be the solution or part of the solution. The links in the original answer and even the Microsoft Data Connector tutorial, while good, don't delve into how to actually get response headers in case pagination information isn't contained in the body of the response.

[lauritxi · Accepted solution]

Hi, 

I understand that they are methods to obtain the request headers, not response headers.

In this article:

https://www.poweredsolutions.co/2017/09/04/guide-and-resources-for-web-data-extraction-with-power-bi...

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.