Power Query Web.Headers() Funktion für Power Bi

Die Web.Headers() Funktion ist meine neueste Entdeckung im Power Query (Excel oder Power Bi) Ökosystem, aber sie kommt leider mit zu vielen Einschränkungen!

Web-Daten via Power Query / Power Bi inkl. HTTP-Response-Headers abfragen

In der Zeit von Reporting-APIs für einschlägige SaaS-Anwendungen wird die Verbindung zwischen Power Bi und dem Web zunehmend wichtiger. In der Power Query M-Programmiersprache steht Analysten dafür die Web.Contents() Funktion zur Verfügung. Mit dieser Funktion kann man entweder einen POST- oder GET-Request an eine API schicken und man erhält das Ergebnis der zugehörigen Response direkt in der Power Query Editor.

Tatsächlich enthält das Ergebnis der Web.Contents()-Funktion aber nur den Response-Body von der Schnittstelle. Manchmal finden sich jedoch wichtige Informationen zur Abfrage auch im Response-Headers. Dafür haben die Entwickler hinter Power Query die Web.Headers()-Funktion entwickelt. Diese kann die Repsonse-Header einer Anfrage anzeigen – aber nicht vollständig. In diesem Beitrag möchte ich deshalb kurz auf die Funktion und die zugehörigen Limitationen eingehen.

Der Anwendungsfall: Post-Informationen von der WordPress API abfragen

Ich werde das Ganze an einem Beispiel aufbereiten, das mich erst auf das Problem aufmerksam gemacht hat: Die Abfrage von Post-Informationen über die internetzkidz.de WordPress API. Hier wollte ich eine Liste von öffentlichen Posts ziehen, um diese in Power Bi weitergehend zu analysieren. Ich bekomme den JSON-Body der Web-Contents-Abfrage problemlos in den Query-Editor. Da die API aber die Abfrage auf maximal 100 Ergebnisse limitiert und eine Paginierung beinhaltet, erhalte ich über die WordPress API einige Informationen nur über den Response Header. Diese sind unter anderem:

• Die Gesamtanzahl der Posts auf der Website (bzw. der Abfrage)
• Die Anzahl der Seiten bzw. die Nummer der letzten Seite für die Query
• Den Link zur nächsten Seite der Abfrage

All das sind wichtige Infos für mich, möchte alle Posts in Power Bi analysieren und einmal durch alle Seiten „loopen“.

Web.Headers() Dilemma Step by Step

HTTP-Response-Body und -Header via Postman

Um das Rückgabe Format der WordPress API zu testen, habe ich den Request via Postman (Desktop) getestet. Dort bekomme ich problemlos den Responder-Body und den Responder-Header angezeigt. Mein API Request ist dabei:

https://internetzkidz.de/wp-json/wp/v2/posts?_fields=title,slug,author,link&per_page=100

Response Body

Hier ist zu erkennen, dass ich 100 Einträge für meine Posts zurückbekomme mit den Felder, die ich im _fields-Parameter angefordert habe.

Response Header

Im Header sind nun die wichtige Infos für den Loop enthalten:

• X-WP-Total zeigt mir die Anzahl der überhaupt verfügbaren Posts für meine Abfrage an
• X-WP-TotalPages gibt die Anzahl der Seiten zurück unter Berücksichtigung des ?per_page=100 Limits
• Link beinhaltet Informationen zur vorherigen (rel=“prev“) und nächsten Seite (rel=“next“) der Seite

Mit den obigen Infos könnte ich nun entweder mit der Anzahl der Beiträge oder der Seiten eine List.Generate()-Funktion in Power Query bauen, die den Request einmal für jede Seite macht. Oder ich könnte mir mit den rel- und next-Infos behelfen. Aber schauen wir erst einmal in die Power Query Äquivalent der Abfrage.

Power Bi Ergebnis mit der Web.Contents() Funktion

Die Web.Contents()-Funktion sollte in Power / Power Bi nun etwas ähnliches tun wie die Postman App. Mit der Abfrage

(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"))

erhalte ich aber lediglich folgende Sicht zurück:

Mit ein bisschen Power Query Expansion Magic wird daraus dann eine brauchbare Tabelle:

Aber: Nirgendwo in dem Ergebnis sehe ich

• Wie viele Posts es (noch) gibt?
• Wie viele Seiten ich einzeln anfragen muss?

Diese Infos sind – wie bereits beschrieben – nur im Response-Header enthalten. Dieser taucht hier aber nicht auf!

Web.Headers() zur Rettung? – Eher nicht!

Für User, die die Response Headers zu einer Abfrage benötigen, gibt es in Power Query die Web.Headers() Funktion. Diese nimmt eigentlich die gleichen Parameter wie die Web.Contents()-Funktion entgegen und liefert mit folgender Formel

(M-Code)

= Web.Headers("https://internetzkidz.de/wp-json/wp/v2/posts?_fields=title,slug,link,author&per_page=100")

das folgende Ergebnis:

Bitter! Die Response-Header, die mir angezeigt werden sind lediglich:

• Allow
• Cache-Control
• Content-Type
• Date
• Expires
• Server
• Vary

D.h. also auch, dass die Response-Header für X-WP-Total, X-WP-TotalPages und Link mir gar nicht in Power Bi angezeigt werden können. Dies ist von Microsoft so gewollt und im nächsten Abschnitt erkläre ich warum.

Limitationen der Web.Headers() Funktion in Power Bi

Die Web.Headers() Funktion gibt lediglich die HTTP-Request-Response-Header für Attribute wie Allow, Cache-Control, Content-Type, Date, Expires, Server, Vary aus Sicherheitsgründen zurück. Eine der größten Befürchtungen im Power Bi Interface ist, dass User Daten wie Passwörter oder andere Credentials einsehen oder veröffentlichen können (was gerade in Konzernen Sinn macht). Entsprechend haben die PBI-Entwickler die sieben Attribute des Headers „gewhitelisted“. D.h. es werden alle Attribute entgegengenommen, aber nur diese sieben werden dem User im Query Editor angezeigt. So wird sichergestellt, dass APIs nicht einfach so Dinge wie Auth-Tokens mit der Response zurück ins Power Bi Interface spielen können.

Dies ist nicht trivial, wenn man bedenkt, dass es nicht ausreichen würde, wenn man einfach Header-Attribute wie „Cookie“ oder „Session“ oder „Auth“ für die Web.Headers() Funktion blocken würde. Schließlich könnten SaaS-Anbieter auch eigene Key-Value Pairs in den Header einspielen wie „XY-Cookie“ oder „XY-Session“ oder „YX-Auth“, die dann wiederum nicht von der Firewall geblockt werden würden.

Entsprechend möchte Power Bi / Microsoft so gut wie keine Verantwortung für Datenschutz- und Sicherheitsthemen annehmen und limtiert die Web.Headers() Funktion (und eigentlich auch die Web.Contents()-Funktion) in dieser Breite. Verständlich aber ärgerlich!

Und jetzt? Einen eigenen Power Query Connector bauen?

Aktuell gibt es drei Möglichkeiten für mich an „frische“ WordPress-Daten zu kommen. Der empfohlene Weg ist es, einen eigenen Power Bi / Power Query Connector zu bauen. Anders als im bestehenden Interface, haben Entwickler eines Connectors nämlich Zugriff auf alle Response-Headers einer Requests (was auch Sinn macht, schließlich benötigen die meisten Standard-Connectoren Authentifizierungen). Also müsste ich recherchieren, wie ich am einfachsten einen Custom Connector code und in meinem System registriere. Ob und wann ich das mache, kann ich noch nicht sagen. Aber wahrscheinlich gibt es einen Blogbeitrag dazu, sollte es passieren.

Die zweite Option ist die Suche nach einem bereits existierenden Custom Connector für die WordPress API. Dazu muss ich mich in den kommenden Tagen womöglich mal auf GitHub oder im Marketplace umschauen.

Auf die Schnelle ist es womöglich am sinnvollsten, ich lade die Daten von WordPress API einmal täglich (automatisiert) auf meinem Rechner oder in die Cloud im JSON-Format. Meine Tools der Wahl wären hier wahrscheinlich Power Automate Desktop, Postman oder Power Automate Cloud Flows. Dies ist tatsächlich einigermaßen easy, aber leider nicht nativ „Power Bi“, sodass das Prinzip nicht mit einem Workbook zu exportieren und weiterzugeben wäre.

Bis dahin: Happy Reporting!