The Web.Headers() function is my latest discovery in the Power Query (Excel or Power Bi) ecosystem, but it unfortunately comes with too many limitations!
Query web data via Power Query / Power Bi incl. HTTP response headers
In the era of reporting APIs for relevant SaaS applications, the connection between Power Bi and the web becomes increasingly important. In the Power Query M programming language, the Web.Contents() function is available to analysts for this purpose. With this function, you can send either a POST or GET request to an API and you will get the result of the corresponding response directly in the Power Query editor.
In fact, the result of the Web.Contents() function contains only the response body from the interface. Sometimes, however, important information about the query can also be found in the response header. For this purpose, the developers behind Power Query have developed the Web.Headers() function. This can display the repsonse headers of a query – but not completely. In this article I would like to briefly discuss the function and its limitations.
The use case: retrieve post information from the WordPress API
I’ll break it down using an example that first made me aware of the problem: Querying post information from the internetzkidz.de WordPress API. Here I wanted to pull a list of public posts to analyze them further in Power Bi. I get the JSON body of the web content query into the query editor with no problem. However, since the API limits the query to a maximum of 100 results and includes pagination, I get some information via the WordPress API only via the response header. These include:
- The total number of posts on the site (or query)
- The number of pages or the number of the last page for the query
- The link to the next page of the query
All this is important info for me, would like to analyze all posts in Power Bi and “loop” through all pages once.
The Web.Headers() dilemma step by step
HTTP response body and header via Postman
To test the return format of the WordPress API, I tested the request via Postman (desktop). There I get the responder body and responder header displayed without any problems. My API request is included:
https://internetzkidz.de/wp-json/wp/v2/posts?_fields=title,slug,author,link&per_page=100Response Body
Here you can see that I get back 100 entries for my posts with the fields I requested in the _fields parameter.
Response Header
The header now contains the important info for the loop:
- X-WP-Total shows me the number of posts available at all for my query.
- X-WP-TotalPages returns the number of pages considering the ?per_page=100 limit
- link contains information about the previous (rel=”prev”) and next page (rel=”next”) of the page
With the above info, I could now either use the number of posts or pages to build a List.Generate() function in Power Query that makes the request once for each page. Or I could make do with the rel and next info. But let’s look into the Power Query equivalent of the query first.
Power Bi result with the Web.Contents() function
The Web.Contents() function should now do something similar to the Postman app in Power / Power Bi. With the query
(M code)
=Web.Contents("https://internetzkidz.de/wp-json/wp/v2/posts?_fields=title,slug,author,link&per_page=100")bzw.
(M code)
=Json.Document(Web.Contents("https://internetzkidz.de/wp-json/wp/v2/posts?_fields=title,slug,author,link&per_page=100"))I only receive the following result:
And with a bit of digging I get down to this:
But: Nowhere in the result do I see:
- How many posts there are (left)?
- How many pages I have to request individually?
This information is – as already described – only contained in the response header. But this does not appear here!
Web.Headers() to the rescue? – Not really!
For users who need the response headers for a query, there is the Web.Headers() function in Power Query. This function actually takes the same parameters as the Web.Contents() function and returns with the following formula:
(M code)
= Web.Headers("https://internetzkidz.de/wp-json/wp/v2/posts?_fields=title,slug,link,author&per_page=100")delivers this result:
That is sad. The response headers that are displayed to me are simply:
- Allow
- Cache-Control
- Content-Type
- Date
- Expires
- Server
- Vary
This means that the response headers for X-WP-Total, X-WP-TotalPages and Link cannot be displayed in Power Bi. This is what Microsoft wants and in the next section I will explain why.
Limitations of the Web.Headers() function in Power Bi
The Web.Headers() function only returns the HTTP request-response headers for attributes like Allow, Cache-Control, Content-Type, Date, Expires, Server, Vary for security reasons. One of the biggest fears in the Power Bi interface is that users can view or publish data such as passwords or other credentials (which makes sense especially in corporations). Accordingly, the PBI developers have “whitelisted” the seven attributes of the header. That is, all attributes are accepted, but only these seven are displayed to the user in the query editor. This ensures that APIs can’t just play things like auth tokens back into the Power Bi interface with the response.
This is not trivial, considering that simply blocking header attributes like “cookie” or “session” or “auth” for the Web.Headers() function would not be sufficient. After all, SaaS providers could also inject their own key-value pairs into the header such as “XY-Cookie” or “XY-Session” or “YX-Auth”, which again would not be blocked by the firewall.
Accordingly, Power Bi / Microsoft wants to take almost no responsibility for privacy and security issues and limits the Web.Headers() function (and actually also the Web.Contents() function) in this width. Understandable but annoying!
Now what? Build a custom power query connector?
Currently there are three ways for me to get “fresh” WordPress data. The recommended way is to build your own Power Bi / Power Query Connector. This is because unlike in the existing interface, developers of a connector have access to all response headers of a request (which makes sense, after all most standard connectors require authentication). So I would have to research how to most easily code a Custom Connector and register it in my system. If and when I do that, I can’t say yet. But there will probably be a blog post about it, should it happen.
The second option is to look for an existing custom connector for the WordPress API. I might have to look around on GitHub or in the Marketplace in the coming days.
In a hurry, it might make the most sense, I load the WordPress API data once a day (automated) on my computer or to the cloud in JSON format. My tools of choice here would probably be Power Automate Desktop, Postman or Power Automate Cloud Flows. This is actually reasonably easy, but unfortunately not natively “Power Bi”, so the principle would not be exportable and shareable with a workbook.
Till then: Happy Reporting!
