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 finden Sie 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, laden Sie sie runter und .. 

API_Test_ValuesInPast_Example.xlsx

Die Verbindung erstellen 

Datenabfrage aus dem Web erstellen

Starten Sie Excel und klicken Sie unter dem Reiter Daten auf Daten abrufen, Aus anderen Quellen, Aus dem Web. 

API-Befehl eintragen

Fügen Sie den Befehlslink der API, den Sie abfragen möchten, 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 finden sie im Testtool unter obenstehendem Link. Dort können Sie auch die ID des von Ihnen gewünschten Gerätes in Erfahrung bringen.

Authentifizierung zum Link (Passwort und Benutzername)

Nun werden Sie 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ählen Sie in Excel einen Platz aus, wo die Eingabe für das Datum erfolgen soll.  Erstellen Sie dazu eine Tabelle unter Einfügen --> Tabelle. (Wichtig)

Wählen Sie 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 markieren Sie die Tabelle und wählen 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. Erstellen Sie eine neue Abfrage in Power Query (Rechtsklick unter Abfragen)
3. Erstellen Sie eine  Leere Abfrage mit dem Namen "Datumsauswahl"


4. Kopieren Sie 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ühren Sie  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

Erstellen Sie eine neue Abfrage mit Rechtsklick auf den Abfragen-Bereich links. Wählen Sie 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. Achten Sie 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.
Sie erhalten so den Namen des Obis Codes und die Einheit der Werte.

Obis Codes (Excel)