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 ..
Herunterladen der Datei
Aktualisieren der Abfrage Login Daten gemäss Informationen in der Tabelle "Dashboard".
API_Test_ValuesInPast_Example.xlsxDie 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.
Wählen Sie den entsprechenden Link
Klicken Sie auf Berechtigungen bearbeiten
Klicken Sie unter Anmeldeinformationen auf Bearbeiten
Geben Sie den Benutzername und das Passwort des Accounts im Tab "Standard" ein
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:
Abfrage innerhalb der Exceltabelle auf die Variable "Datum".
Abfrage vom Web mit einem passenden API Befehl. Hier passend ist der Befehl https://smart-me.com/api/ValuesInPast/{id} (Tages-Zählerdaten aus der Vergangenheit)
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:
Öffnen Sie Power Query
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.
Den Inhalt in eine Tabelle konvertieren
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.