Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lauritxi
Frequent Visitor

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.

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

 

 

 

View solution in original post

11 REPLIES 11
TomTomTom
Helper II
Helper II

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
Resolver II
Resolver II

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:

 

Screen Shot 2018-05-23 at 9.32.35 AM.png

 

Back in Power Query, Value.Metadata on the response exposes the value for the "Content-Type" header, but nothing else.

Screen Shot 2018-05-23 at 9.44.50 AM.png 

 

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?

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
Not applicable

Hi, 

 

So there exists a workaround to get the response header? 

Can you give a bit more precise information, please?

 

regards,

Axel Vulsteke

Hi @Anonymous,

 

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

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:

Custom%20Connector%20Power%20BI%20PQ%20Section%20Seattle%20Metadata

 

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

Custom%20Connector%20Power%20BI%20Results%20Metadata

 

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

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
Not applicable

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.

Why custom connector is able to get the response header?

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.

 

 

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.

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors