Power Query

Schritt 1 – Bereitstellung der Datenquellen

Zunächst einmal wird ein Speicherort für die Datengrundlage benötigt. Wenn dieser angelegt ist, öffnen Sie eine leere Excel-Arbeitsmappe und wählen über den Menübandreiter „Daten“ unter der Befehlsgruppe „Abrufen und transformieren“ den Befehl „Neue Abfrage“ aus. Hier können Sie aus einer Vielzahl von unterschiedlichen Datenquelltypen auswählen. In diesem Beispiel liegen uns zwei CSV-Dateien und eine Excel-Arbeitsmappe vor. Wir beginnen mit den CSV-Dateien und wählen daher die entsprechende Quelle aus.

Es öffnet sich das Explorer-Fenster und anschließend wird zur entsprechenden Datenquelle navigiert. Jetzt öffnet sich Power Query bzw. das Datenvorschaufenster von Power Query. Hier erhalten Sie eine Vorschau, wie die Daten in der Abfrage dargestellt werden. Je nach CSV-Codierung müssen der korrekte Dateiursprung und das richtige Trennzeichen ausgewählt werden. In diesem Fall der Unicode UTF-8 und das Komma als Trennzeichen. Nach jedem Komma wird nun der Datensatzinhalt in je eine neue Spalte geschrieben. Über einen Klick auf „Daten transformieren“ gelangen Sie jetzt in den Power Query Editor.

Im Editor werden alle Datensätze aus der CSV-Datei geladen und so dargestellt, wie in der Vorschau angezeigt. Im Editor bietet Power Query im Menübandreiter eine Werkzeugleiste mit unzähligen Bearbeitungsfunktionen an, um die Daten ganz nach Wunsch zu transformieren. Einen vollständigen Einstieg in Power Query mit allen Befehlsfunktionen und Anwendungsbeispielen können Sie im Power Query Online-Video-Kurs erhalten.

Nachdem die erste CSV-Datei mit den Umsatzdaten in der Power Query Abfrage geladen wurde, wird jetzt noch die zweite CSV-Datei mit den Absatzdaten benötigt. Aus dem Editor heraus, kann über den Menübandreiter „Start“ unter der Befehlsgruppe „Neue Abfrage“ direkt der Befehl „Neue Quelle -> Datei -> Text/CSV“ ausgewählt und zur zweiten CSV-Datei navigiert werden. Der Schritt über das Vorschaufenster wiederholt sich und kann genau so ausgeführt werden. Jetzt befinden sich die beiden CSV-Dateien im Editor. Fehlt nur noch der Lagerbestand aus der Excel-Arbeitsmappe.

Dazu wird erneut eine neue Quelle ausgewählt, nur diesmal nicht als CSV sondern als Excel. Bei Excel-Arbeitsmappen müssen im Vorschaufenster keine Codierung und Trennzeichen ausgewählt werden, lediglich das Tabellenblatt, auf dem sich die Daten befinden. Nach der Bestätigung mit „OK“ befinden sich auch die Daten der Lagerbestände im Power Query Editor. Insgesamt finden sich jetzt drei Abfragen im Editor, die im nächsten Schritt noch zusammenführt werden müssen.

Im Editor finden sich die 3 Abfragen: Umsatz, Absatz und Lagerbestand

Schritt 2 – Abfragen zusammenführen

Zunächst einmal steht die Überlegung an, welche Daten letztendlich benötigt werden, um das gewünschte Ergebnis darstellen zu können. Ziel soll es sein, die Umsatz-, Absatz- und Lagerbestandsentwicklung je Monat aufzuzeigen.

Die Datengrundlagen sind allesamt so aufgebaut, dass in der ersten Spalte das Jahr, in der zweiten Spalte der Monat, in der dritten Spalte die Artikelnummer, in der vierten Spalte die Produktgruppe und  in der fünften Spalte der jeweilige Wert steht.

Damit die Werte in einer 1-zu-1-Beziehung zusammengeführt werden können, wird ein einheitliches Suchkriterium benötigt. Dieses Suchkriterium muss eine Kombination aus Zeitpunkt und Artikelnummer darstellen. Mit Power Query lässt sich so eine Kombination ganz einfach in einer Abfrage erstellen. Was in Excel unter der VERKETTEN-Funktion bekannt ist, heißt in Power Query „Spalten zusammenführen“.

Dazu werden die Spalten „Jahr“, „Monat“ und „Artikelnummer“ markiert und anschließend über den Menübandreiter „Spalte hinzufügen“ der Befehl „Spalten zusammenführen“ ausgeführt. Es öffnet sich ein Dialog-Fenster, indem noch ein Trennzeichen zwischen den Attributen sowie ein Name für die neue zusammengeführte Spalte definiert werden kann.

In diesem Beispiel wurde ein Mittestrich als benutzerdefiniertes Trennzeichen ausgewählt. Das Ergebnis ist eine neue Spalte mit einer Zusammenführung aus Jahr, Monat und Artikelnummer.

Diese Spalte dient nun als Suchkriterium für die Zusammenführung mit den anderen Abfragen. Voraussetzung ist natürlich, dass jede Abfrage diese zusammengeführte Spalte enthält. Somit muss dieser Vorgang auch für die Abfragen Absatz und Lagerbestand durchgeführt werden.

Im nächsten Schritt erfolgt die eigentliche Zusammenführung der Daten. Dazu wird eine der drei Abfragen ausgewählt und über den Menübandreiter „Start“ unter der Gruppe „Kombinieren“, der Befehl „Abfragen zusammenführen“ ausgewählt. Es öffnet sich ein Dialogfenster. Im oberen Bereich befindet sich die Datenvorschau der aktuellen Abfrage. Darunter findet sich ein Drop-Down-Feld. Hier wird die Abfrage ausgewählt, die zusammenführt werden soll. In diesem Fall wird zunächst die Abfrage „Absatz“ ausgewählt.

Als nächstes wird ein sogenannter „Join“ benötigt, also eine Verbindung. Der Join ist im Prinzip gleichzusetzen mit dem Suchkriterium, wie er aus der SVERWEIS-Funktion bekannt ist. Jetzt wird im oberen und im unteren Bereich jeweils die Spalte mit dem gemeinsamen Suchkriterium markiert und mit „OK“ bestätigt.

Im Power Query Editor findet sich jetzt in der Abfrage „Umsatz“ eine neue Tabellenspalte mit dem Namen „Absatz“. Durch Klicken auf das Erweiterungssymbol öffnet sich eine Liste mit allen Spalten aus der Abfrage „Absatz“. Hier kann nun ausgewählt werden, welche Spalten aus der Abfrage „Absatz“, zu der Abfrage „Umsatz“ zusammengeführt werden sollen. Für das Ergebnis ist lediglich der Absatz relevant.

Das Häkchen bei „Ursprünglichen Spaltennamen als Präfix verwenden“ kann beliebig gesetzt oder entfernt werden. Dadurch wird der Name aus der zusammengeführten Abfrage dem Spaltennamen voran gesetzt, um bei größeren Kombinationen nicht den Überblick zu verlieren. Nach der Bestätigung mit „OK“ findet sich die neue Spalte „Absatz“ in der Abfrage „Umsatz“.

Dieses Prozedere wird jetzt noch einmal mit dem Lagerbestand wiederholt. Sind alle Daten zusammenführt, kann der Power Query Editor über „Schließen & laden“ verlassen werden. Jede im Editor erstellte Abfrage wird dabei in einem eigenen Tabellenblatt in der Excel-Arbeitsmappe geladen.