Datensätze der letzten 45 Tage via PowerQuery in Excel abrufen

[Moritz]

In der Diskussion um die Möglichkeit, Datensätze über die API für einen bestimmten Zeitraum abrufen zu können stellte @Jens Mussler die Frage, wie in einer Excel-PowerQuery Datensätze der letzten 45 Tage abgerufen werden kann. Die Lösung hattest du – Jens – dann im weiteren Verlauf der Diskussion dann ja sogar bereits mitgeliefert...

Am 28.11.2022 um 18:42 schrieb Jens Mussler:

Habe in einer anderen Excel-Datei folgenden Quelle gefunden:

/Backend/preview/Smaps/{smapId}/Records?$filter=CompletedDate gt "&DateTime.ToText(Date.AddDays(DateTime.LocalNow(), -45), "yyyy-MM-ddT00:00:00Z")&"&accessToken={API-Token}"),

Die Lösung ist absolut tauglich:

• Man baut sich einfach im Vorfeld die ganze URL zusammen, zunächst mit einem statischen Datum (so wie in der Swagger-API-Dokumentation zu finden).
• Dann ersetzt man den Zeitstempel-Teil einfach durch obige Formeln, die mit Hilfe der Excel-Textverbinder (&) in die bestehende URL-integriert wird.

Es muss indes ggf. darauf geachtet werden, dass die Uhrzeit (00:00:00Z) eine UTC-Zeitangabe ist und entsprechend unserer Sommer-/Winterzeit auf die eigene Zeitzone angepasst werden muss/müsste (sofern beispielsweise alle Vorgänge sowieso immer erst um 8 Uhr beginnen, ist die UTC-Angabe natürlich vollkommen ausreichend).

Jens bat dennoch um weitere Hilfe, da die Umstellung nicht ganz einfach funktioniere. Tatsächlich ist es auch nicht ganz trivial, wenn man von dem regulären Datenabruf (über die v1_Web oder die interne API) auf den Abruf via Preview-API umstellt. Hintergrund ist, dass die Daten bei letzterer nicht als einfaches Array aus JSON-Objekten returniert wird.

Stattdessen wird das Resultat hier mit weiteren OData-Metadaten angereichert:

Für den Excel-Abruf via PowerQuery hat das unmittelbare (und nach meiner Einschätzung auch unschöne) Auswirkungen: Da die Daten nicht wie gewohnt direkt als Array aus Objekten eingeht...

... sondern nun zunächst ein Zwischenschritt zum Extrahieren der Liste aus dem Attribut "value" erforderlich ist...

... führt ein Klick auf "List" dazu, dass alle möglicherweise bereits ausgeführten Operationen/Transformationen mit den Daten hinfällig werden und neu aufgebaut werden müssen 😞

Zumindest ist mir kein Trick bekannt, bei dem ich dies umgehen könnte. @Thomas Hoffmann und @Moritz Münzenmaier, ihr träumt doch üblicherweise Dienstag- bis Donnerstagnachts in PowerQuery-Syntax (🤪) — habt ihr eine Idee, wie man das geschmeidiger lösen könnte?

Ich hatte bereits einen Zwischenschritt eingefügt (Rechtsklick auf Quelle in den angewendeten Schritt und dann "Schritt danach einfügen"), aber auch dann führt das Extrahieren der Liste zum gleichen Ergebnis.

[Moritz]

Bzw. wenn ihr es selber nicht wisst. Wen kennt ihr, den wir hier brauchen, der uns diese Frage beantworten kann? 😉 

[Moritz]

Oder @Michael Klaus?

[Moritz Münzenmaier]

Hey @Moritz - Danke für die Markierung. Ich kann die Frage dahinter leider nicht beantworten, da der oData Feed tatsächlich nicht mein Steckenpferd ist. 😞 

[Moritz]

@Jens Mussler ich befürchte in dem Fall musst du die Schritte, die nach der Extraktion der Daten aus der smapOne-API ausgeführt werden sollen, nochmal nachbauen, nachdem der Abruf auf oData (= Preview-API) umgestellt wurde.

[Jens Mussler]

@Moritz ich habe die Datei am Freitag geöffnet, um die Daten vom 1.-15.12. abzurufen. Habe zuvor die Extraktion der Daten ausgeführt, allerdings haben sich die Daten dann nicht aktualisiert!

Ich werde mir die Datei diese, spätestens nächste Woche anschauen und mich wieder melden.

[Moritz]

@Jens Mussler ja, guck einmal rein und melde dich dann nochmal. Wir schaffen das! 💪🏼🙂

[Jan F.]

Über den Erweiterten Editor konnte ich den Schritt "Navigation" einfügen, ohne weitere Schritte zu entfernen.

Anders gesagt, ich konnte den  "/Backend/intern..."-Abruf durch den "/Backend/preview..."-Abruf ersetzen.

 

Hier einmal der Abruf + einfache Formatierung mit Datensätzen über "/Backend/intern...":

 

Hier einmal der Abruf + einfache Formatierung über "/Backend/preview...":

 

Zum Umstellen habe ich die URL ersetzt, die grüne Zeile (bzw. den Schritt "Navigation") eingefügt und in der folgenden Zeile #"Datensätze abgerufen" durch value ersetzt.

 

Und das wars auch schon.

 

[Moritz]

Starkes Stück, danke für die Lösung!

[Johanna Becker]

@Jan F. WOW! Danke fürs Teilen!!