Power Query in Excel - Automatisierte Datenaufbereitung für Datenanalysen, Reports und Dashboards

Teil II Datenintegration mit Power Query

In diesem Teil lernen wir die Benutzeroberfläche von Power Query kennen, mit der wir Abfragen erstellen und verwalten. Abfragen sind Kernelemente von Power Query. Wir erfahren, welche Bedeutung Transformationsschritte haben und erhalten einen ersten Einblick in die Skriptsprache M Language.

4 Gründe für Power Query

Die Arbeiten in der Datenintegration können viel wertvolle Zeit beanspruchen und sich als ineffizient herausstellen, wenn diese manuell und wiederkehrend verrichtet werden.

Abb. 4-1: Die Datenintegration im Vierschichtenmodell

Die aus den Datenquellen gewonnenen Daten sind nicht immer passend strukturiert oder müssen inhaltlich korrigiert werden. Das Entfernen von Spalten, die Extrahierung von Teilinformationen, das Teilen von Tabellenfeldern, das Ersetzen oder Übersetzen von Begriffen, das Verdichten auf einer tieferen Datentiefe, die Zusammenführung oder das Anfügen von verschiedenen Datenquellen, das Hinzufügen von Berechneten Spalten sind nur ein paar Beispiele solcher Arbeiten.

Seit der Version 2016 ist Power Query in Excel integriert. Power Query enthält eine Fülle an Befehlen, mit denen sich die vorhin beschriebenen Datenintegrationsarbeiten bequem erledigen lassen - und zwar auf der Benutzeroberfläche und ganz ohne Programmierung. Automatisierungen in der Datenintegration sind natürlich immer noch mit der Skriptsprache Visual Basic for Applications (VBA) realisierbar. Man muss diese Skriptsprache jedoch sehr gut kennen. Aufgrund der Komplexität gewisser Datentransformationen müssen manchmal VBA-Experten hinzugezogen werden. Wenn Anpassungen an der Transformationslogik erforderlich werden, sind wir wieder abhängig von anderen.

Mit Power Query hingegen wird der Self-Service Ansatz konsequent durchgesetzt. Wir können die Transformationslogiken selbstständig und unabhängig direkt in Power Query erstellen.

Mit Power Querys Standardkonnektoren können wir Daten aus den unterschiedlichsten Dateiformaten, aus Datenbankservern verschiedener Hersteller sowie aus Onlinediensten beziehen. Die Herstellung einer Verbindung zu diesen Datenquellen ist dank einer sehr guten Benutzerführung stark vereinfacht worden. Die Verbindungen bleiben bestehen, so dass eine Aktualisierung auf den neuesten Stand in Excel jederzeit möglich ist.

Mit Power Query erstellen wir Abfragen, welche immer wieder aufgerufen werden können, wenn sich etwas in der Datenquelle verändert hat. Dafür benutzen wir den Befehl «Aktualisieren» in Excel, direkt in der Pivot-Tabelle oder in dynamischen Tabellen. Wir müssen also unsere Arbeitsoberfläche nicht verlassen, um einen Aktualisierungsvorgang zu initialisieren.

Ein wichtiger Bestandteil des Aktualisierungsvorganges ist die Transformationslogik. Sie enthält sämtliche Datenanpassungen an Inhalten und Struktur. Bei manuellen Datenaufbereitungen nimmt diese Aufgabe, je nach Anzahl notwendiger Einzelschritte, sehr viel Zeit in Anspruch, welche anschliessend für die Datenanalyse fehlt. Ausserdem liegt auch eine entsprechend hohe Fehleranfälligkeit vor. In Power Query klicken wir uns die Transformationslogik zusammen, die für uns aufgezeichnet wird, analog zur Aufnahme eines VBA-Makros. Die Anzahl Einzelschritte kann beliebig gross sein. Wir können sogar den jeweiligen Datenzustand nach jedem Einzelschritt einsehen. Alle einzelnen Transformationsschritte werden zu einer Logik aneinandergekettet und bei jeder Aktualisierung vom ersten bis zum letzten Schritt vollständig ausgeführt.

Power Query nimmt in der Business Intelligence Strategie von Microsoft einen immer bedeutenderen Platz ein und wird laufend weiterentwickelt, sowie in verschiedenen Applikationen integriert. Power Query ist Bestandteil von Power BI, dem Business Intelligence System von Microsoft. Alles, was wir hier in der Anwendung von Power Query lernen, können wir in Power BI verwenden.

5 Power Query starten

Wir werden in Excel in keinem Menüband den Begriff «Power Query» vorfinden. Um mit Power Query zu arbeiten, gehen wir auf das Menübandregister «Daten». In der Gruppe «Daten abrufen und transformieren» finden wir die Power Query Befehle.

Abb. 5-1: Power Query ist «Daten abrufen und transformieren» in Excel

Über den Befehl «Daten abrufen» können wir mit der Erstellung einer neuen Abfrage in Power Query beginnen.

Mit Power Query erstellen wir Abfragen - sie sind der Kern von Power Query. Abfragen enthalten den kompletten ETL-Prozess (vgl. Kapitel 1 «Business Intelligence») und werden in der Arbeitsmappe gespeichert.

Abb. 5-2: Daten abrufen in Excel

1. In diesen Befehlsgruppen befinden sich Standardkonnektoren, welche eine einfache Verbindung zu den unterschiedlichsten Datenquellen ermöglichen. Diese werden eingehend im Teil III «Externe Datenquellen einbinden» behandelt.
2. In Power Query können Tabellen mit Inhalten anderer Tabellen ergänzt werden oder an andere Tabellen angehängt werden. Der Befehl «Abfragen kombinieren» macht dann Sinn, wenn wir mindestens zwei Abfragen erstellt haben (vgl. Kapitel 37.5 «Abfragen zusammenführen» und Kapitel 39 «Abfragen anfügen»).
3. Wir können den Power Query Editor starten, ohne eine Verbindung zu einer Datenquelle erstellen zu müssen (vgl. Kapitel 6 «Der Power Query Editor»). Das können wir anschliessend direkt im Power Query Editor erledigen.

Vielleicht wird bei Ihnen zusätzlich die Befehlsgruppe «Legacy-Assistenten» angezeigt. Hiermit können Sie herkömmliche Befehle für Datenimporte aufrufen, jedoch keine Power Query Abfragen erstellen.

Abb. 5-3: Legacy-Assistenten

Wir deaktivieren bzw. aktivieren die Legacy-Assistenten in den Excel-Optionen.

Abb. 5-4: Die Excel-Optionen «Legacy-Datenimport»

Nachdem wir einen Standardkonnektor ausgewählt haben, zeigt uns Power Query im nächsten Schritt eine Vorschau an (vgl. Abb. 5-5). Der Inhalt der Vorschau ist abhängig vom Konnektor. Für das Beispiel in Abb. 5-5 haben wir eine Verbindung zu einer Textdatei hergestellt. Im Teil III «Externe Datenquellen einbinden» werden wir die unterschiedlichen Vorschauinhalte der einzelnen Konnektoren näher betrachten.

Abb. 5-5: Die Datenvorschau vor dem Laden externer Daten

Über den Befehl «Daten transformieren» (bzw. in älteren Versionen mit «Bearbeiten» bezeichnet) gelangen wir zum Power Query Editor.

6 Der Power Query Editor

In diesem Kapitel befassen wir uns mit der Benutzeroberfläche des Power Query Editors.

Abb. 6-1: Die Benutzeroberfläche von Power Query

1. Das Menüband des Power Query Editors
   In vielen Fällen reicht die Nutzung der Transformationsbefehle aus dem Menüband. Weiter unten gehen wir auf die einzelnen Register des Menübandes ein.
2. Der Navigatorbereich «Abfragen»
   Der Navigatorbereich listet sämtliche Abfragen der aktuellen Arbeitsmappe auf. Eine Arbeitsmappe kann gleichzeitig mehrere Abfragen beinhalten, was insbesondere dann zwingend ist, wenn wir verschiedene externe Datenquellen benötigen. Die im Navigatorbereich enthaltenen Abfragen können wir uns auch in Excel anzeigen lassen (vgl. Kapitel 8 «Abfragen in Excel anzeigen»).
3. Die Bearbeitungsleiste
   Jedem Transformationsschritt liegt eine Codezeile in der Skriptsprache M Language zugrunde. Was im aktuellen Transformationsschritt genau ausgeführt wird, kann man sich in der Bearbeitungsleiste anzeigen lassen.
4. Die Datenvorschau
   Jeder Transformationsschritt nimmt eine Anpassung der Daten vor. Wie die Daten nach dieser Anpassung aussehen, können wir uns anzeigen lassen, indem wir auf den Transformationsschritt klicken.
5. Der Name der Abfrage
   Jede Abfrage muss einen eindeutigen Namen erhalten, weil man auf Abfragen referenzieren und deren Ergebnisse in anderen Abfragen nutzen kann.
6. Die angewendeten Schritte
   Sämtliche Transformationsschritte, welche wir mit Hilfe der Befehle auf der Benutzeroberfläche erstellen, werden einzeln aufgezeichnet und in dieser Liste aufgeführt.

An dieser Stelle verschaffen wir uns einen Überblick zum Menüangebot in Power Query. Die einzelnen Befehle werden wir in den jeweiligen Kapiteln im Detail behandeln.

Das Menübandregister «Start» beinhaltet unterschiedliche Befehle: die gängigsten Transformationsbefehle, den Aggregationsbefehl, Tabellenkombinationen, etc.

Abb. 6-2: Das Register «Start» im Power Query Editor

Im Register «Transformieren» wählen wir aus unterschiedlichen Transformationsfunktionen aus, einige davon abhängig vom Datentyp der markierten Tabellenspalte. Der Befehl, den wir hier auswählen, verändert die markierte bzw. die markierten Spalten.

Abb. 6-3: Das Register «Transformieren» im Power Query Editor

Wenn wir die Rohdaten mit zusätzlichen Spalten anreichern, nutzen...