Microsoft Excel

In Microsoft Excel ist es möglich, Daten aus unserer Cloud direkt einzulesen. Dies eröffnet zum Beispiel die Möglichkeit, Daten aus verschiedenen Accounts in einer Datenbank zusammenzuführen, auszulesen und zu verarbeiten.
In Excel wird dafür der Power Query-Editor verwendet. Mit diesem können HTTP-Abfragen mittels unserer API gemacht werden. Die  möglichen API-Befehle und Testumgebung findest du hier. 

Download Beispieldatei

Die Datei  auf der rechten Seite enthält eine variable Abfrage von Zählerständen eines Meters. Diese Datei kann mit weiteren Zählern ergänzt werden.

Alle nötigen Funktionen stehen bei Excel 0365 oder ab Excel 2016 zur Verfügung.

Um die Beispiel-Datei nutzen zu können, lade sie runter und .. 

API_Test_ValuesInPast_Example.xlsx

Die Verbindung erstellen 

Datenabfrage aus dem Web erstellen

Starte Excel und klicke unter dem Reiter Daten auf Daten abrufen, Aus anderen Quellen, Aus dem Web. 

API-Befehl eintragen

Füge den Befehlslink der API, den du abfragen möchtest, ein.  Im Beispiel ist es der Befehl https://www.smart-me.com/api/Devices/{id} . Wir möchten also alle aktuellen Daten des Gerätes mit der jeweiligen ID auslesen.

Mehr Informationen zum Befehl selbst findest du im Testtool unter obenstehendem Link. Dort kannst du auch die ID des von dir gewünschten Gerätes in Erfahrung bringen.

Authentifizierung zum Link (Passwort und Benutzername)

Nun wirst du aufgefordert, die Authentifizierung des jeweiligen Links anzugeben. Es werden der Benutzername und das Passwort des entsprechenden Accounts benötigt.

Die Daten im Power Query Editor in eine Tabelle konvertieren

Im Anschluss an den Import entsteht im Power Query Editor eine Liste der importierten Daten. Diese Daten müssen nun in eine Tabelle konvertiert werden.


Schliessen und Laden

Nach dem Schliessen und Laden entsteht ein neues Tabellenblatt mit den Informationen der Datenquelle.

Verbindungsabfrage Einstellungen (Intervall und Aktualisierung)

Auf der rechten Seite öffnet sich ein Fenster, das mittels Rechtsklick auf die vorhandene Verbindung weitere Einstellungen zulässt. Hier können vor allem Aktualisierungsintervalle der jeweiligen Verbindung festgelegt werden.
Im Tab Daten können auch Aktualisierungen auf Benutzerbefehl geschehen.

Datenabfrage mit Variablen (Zählerstände abfragen mit variablem Datum)

Die Datenabfrage von Vergangenheitsdaten folgt dem gleichen Prinzip wie der Linkaufbau von den aktuellen Daten. Der Hauptunterschied dabei ist, dass Daten mit einer veränderbaren Information (Variable) abgefragt werden müssen.
Damit dies möglich wird, müssen zwei Abfragen gemacht werden:

Erstellen der Datum-Variable

Wähle im Excel einen Platz aus, wo die Eingabe für das Datum erfolgen soll.  Erstelle dazu eine Tabelle unter Einfügen --> Tabelle. (Wichtig)

Wähle einen Bereich von 4 Feldern aus, damit jeweils ein Spaltenname und der Text inkl. dem Wert platz finden.

Tabellenname für spätere Programmierung definieren

Damit Power Query später weiss, in welcher Tabelle die Variable zu finden ist, wird dieser mit dem Namen aufgerufen. Damit dies eindeutig ist, vergeben wir einen fixen Namen (hier Datumsauswahl).

Variabelnfeld formatieren (Textfeld)

Damit das Datum später auch verwendet werden kann, muss der Inhalt als Text formatiert werden. Dazu markierst du die Tabelle und wählst oben das Format Text aus.

Die Variable in Power Query abfragen

Nun können wir die Abfrage in Power Query für unsere Variable hinzufügen:

2. Erstelle eine neue Abfrage in Power Query (Rechtsklick unter Abfragen)
3. Erstelle eine Leere Abfrage mit dem Namen "Datumsauswahl"


4. Kopiere folgenden Text in den Funktionsblock der Abfrage: = Excel.CurrentWorkbook(){[Name="Datumsauswahl"]}[Content]
"Datumsauswahl" ist hier der Name der Tabelle, in welcher der Wert der Variable gefunden werden kann.

Variable und Excelwert verlinken

Führe einen Drilldown durch, um das Feld auszuwählen, in dem der veränderbare Parameter steckt:
Feld mit dem Datumswert auswählen --> Rechtsklick --> Drilldown.

Danach steht der Inhalt der Zelle alleine da und hört von nun an auf den Namen "Datumsauswahl".

Abfrage für die Vergangenheitsdaten erstellen

Erstelle eine neue Abfrage mit Rechtsklick auf den Abfragen-Bereich links. Wähle danach eine Abfrage aus dem Web.

Die neue Abfrage enthält nun den Befehl für vergangene Daten und sieht wie folgt aus:

https://smart-me.com:443/api/ValuesInPast/32b30ab1-3ac5-4fd5-b24f-96d02d3b2bed?date=01.01.2021

Sie beinhaltet den Pfad der HTTP-Abfrage und am Ende ein Zieldatum. Dieses Zieldatum werden wir später variabel mitgeben. 

Zur Erstellung kann ein fix kodiertes Element mitgegeben werden. Achte darauf, dass zu diesem Datum bereits Daten auf der Cloud existieren.

Das Datum hat folgendes Format: Monat.Tag.Jahr bzw. mm.dd.yyyy

Die Variable in die Abfrage einbetten

Damit nun das fixe Datum durch unsere Variable ersetzt wird, muss der Funktionsbefehl etwas angepasst werden.

Es ändert sich von

= Json.Document(Web.Contents("https://smart-me.com:443/api/ValuesInPast/32b30ab1-3ac5-4fd5-b24f-96d02d3b2bed?date=01.01.2021))

zu

= Json.Document(Web.Contents("https://smart-me.com:443/api/ValuesInPast/32b30ab1-3ac5-4fd5-b24f-96d02d3b2bed?date="&Datumsauswahl))


Tabelleninhalt an Bedürfnisse anpassen

Nun kann innerhalb des Datensatzes der angezeigte Inhalt umstrukturiert und an das jeweilige Bedürfnis angepasst werden.

In unserem Beispiel möchten wir gern alle Daten mit DeviceId, Datum, Obis-Code und Wert versehen haben.


2.  Zeilen und Spalten vertauschen

3. Erste Zeile als Überschriften verwenden

4. Values -Spalte bearbeiten und Auf neue Zeilen ausweiten

5. Zusätzliche Zeileninhalte auswählen --> OK.

6. Schliessen und Laden drücken

Obis Codes interpretieren und zuordnen

Die Obis-Codes sind standardisiert. Um diese zuordnen zu können kann die Excelliste mit den Obis-Codes mit SVERWEIS abgeglichen werden.
Du erhältst so den Namen des Obis Codes und die Einheit der Werte.

Obis Codes (Excel)