Microsoft Power Query – nie wieder SVERWEIS!

Was ist Power Query?

Power Query ist seit Excel 2016 verfügbar und ab der Version 2010 mittels des AddIns nachinstallierbar. Es stellt einem umfangreiche Möglichkeiten zur Vorbereitung von Daten zur Verfügung. Zwei Tabellen zusammenbringen, über mehrere Spalten per Knopfdruck? Easy. Die Spalten vorher beim Import filtern? Zwei Klicks. Tabellen mit mehreren Millionen Datensätzen? Ja! Wie das geht zeige ich am Ende des Eintrags.

Was mich als Auditor dann noch besonders freut: Die Schritte werden in einem Audit Trail aufgezeichnet und können leicht reproduziert und zur effizienteren Arbeit kopiert werden. Das Problem, dass man die eigene Excel-Auswertung nach fünf Tagen den Kolleg:innen nicht mehr erklären kann, ist damit zumindest abgeschwächt.

Power Query und der dazugehörige Editor ist eigentlich nur die visuelle Darstellung der M Formula Language. Der Vorteil: Der Einstieg mittels der "Buttons" fällt leicht, komplexere Umwandlungen können auch per Programmiersprache vorgenommen werden. Der Anwenderkreis ist so aus meiner Sicht recht breit.

Wie starte ich Power Query? Der Datenimport

Im Reiter Daten befindet sich der Einstieg zum Power Query-Editor. Da dieser am Anfang noch keine Daten enthalten kann, wird der Klick eher auf einen Import aus einem Text/CSV fallen. Es öffnet sich ebenfalls Power Query, allerdings erst mit einem Assistenten.

Für diesen Beitrag habe ich zwei .txt-Dateien vorbereitet, die in ihrer ursprünglichen Form aus einem SAP-System mit Testdaten stammen: Die Belegköpfe (Tabelle BKPF) und die Belegsegmente (BSEG). Ich habe sie ein wenig manipuliert, damit sie für das Beispiel gute Ergebnisse geben. Wir wollen wissen, ob es Datensätze in der Tabelle BKPF gibt, die nicht in der Tabelle BSEG vorkommen.

• Die Tabelle BKPF finden Sie hier. (87 KB)
• Die Tabelle BSEG finden Sie hier. (607 KB)

Die Daten importiere ich mittels eines Klicks auf "Aus Text/CSV". Nach der Auswahl der Datei, die bei mir zur Einfachheit auf dem Desktop liegt, öffnet sich der neue Datenimport-Assistent. Wichtig ist, dass "Daten transformieren" gewählt wird. So gelangen wir in Power Query, wo wir noch weitere Schritte ausführen werden.

Wie bearbeite ich die Tabelle? Die Datenmanipulation

Die importierten Daten müssen von uns noch bearbeitet werden:

• die führende Spalte ist leer und kann gelöscht werden

• die letzte Spalte kann ebenfalls gelöscht werden

• die ersten drei Reihen sind leer und obsolet

• in der vierten Reihen stehen Spaltennamen, die wir nutzen möchten

Zum Löschen klicke ich einfach die erste Spalte an (sie wird komplett grün eingefärbt) und wähle im Menü "Spalten entfernen" aus. Alternativ hätte ein Rechtsklick auf den Spaltennamen und "Entfernen" dasselbe Ergebnis gebracht.

Nun zum Entfernen der ersten drei Reihen und der Verwendung der Spaltennamen. Dafür einfach einen Filter auf die Spalte Column2 setzen und die leeren Inhalte ausblenden. Achtung, das klappt natürlich nur bei Spalten, die eigentlich keine leeren Werte enthalten dürfen. Die Datenbank von SAP erfordert immer die Angabe eines Mandanten, daher kann in diesem Beispiel auf der Spalte gefiltert werden.

Übrigens rechts sind die „Angewendeten Schritte“ zu sehen. Das ist der angesprochene Audit Trail. Für jeden Klick wird prinzipiell ein Schritt eingefügt. Ein wenig anders verhält es sich bei unserem Entfernen der Spalten, da wir diesen Schritt nur um eine zweite Spalte ergänzen. Dafür gibt es keinen separaten Schritt, die Ergänzung ist ganz gut in der Formelzeile zu sehen.

Der Unterschied zwischen gut und gut gemeint

Anschließend wird im Menü der Button „Erste Zeile als Überschriften verwenden“ ausgewählt. Was fällt auf? Die Datentypen wurden von Excel angepasst. Das ist auch rechts in den „Angewendeten Schritten“ zu erkennen. Im Falle der Überschriften werden zwei neue Schritte eingefügt: Neben dem Hochstellen der ersten Zeile wird auch noch ein Schritt „Geänderter Typ“ hinzugefügt. Excel errät jetzt aufgrund der ersten 1000 Zeilen, welcher Datentyp vermutlich in einer Spalte enthalten ist.

An meiner Formulierung erkennt man hoffentlich meine fehlende Überzeugung. Dieser Automatismus ist gut gemeint, aber nicht gut. In der Praxis bringt er mehr Probleme, als einen schnellen Erfolg. Ich lösche den Schritt daher immer. Er lässt sich auch im Menü permanent deaktivieren. Darauf habe ich aber verzichtet, da ich ansonsten für Seminare diesen Vorführeffekt immer erneut aktivieren müsste.

Hasta la vista SVERWEIS

Die oben beschriebenen Schritte habe ich für die Tabelle BKPF ebenfalls durchgeführt. Dafür muss Power Query nicht verlassen werden. Einfach auf der rechten Seite das Abfrage-Fenster aufklappen und per Rechtsklick eine neue Abfrage hinzufügen:

Um die beiden Tabellen zusammenzufügen benötigen wir ebenfalls das Abfrage-Fenster. Dabei wird allerdings im Menü „Kombinieren“ und „Abfragen als neue Abfrage zusammenführen“ ausgewählt. Im sich öffnenden Dialog wählen wir die beiden Tabellen aus – jeweils über das Dropdown-Menü.

Excel muss jetzt nur noch von uns erfahren, über welche Spalten eine Verbindung stattfinden soll. Anders als bei einem SVERWEIS ist es egal, ob diese Spalten am Anfang der Tabelle stehen. Außerdem kann ich auch mehrere Spalten auswählen. Entweder in dem ich Shift gedrückt halte und die letzte Spalte auswähle oder einzeln bei einer gedrückten Strg-Taste auswähle.

Da unser Ziel ist, die Datensätze zu ermitteln, die in der Tabelle BKPF vorkommen, jedoch nicht in der Tabelle BSEG, müssen wir noch einen passenden Join wählen. In unserem Fall ist das der Linke-Anti-Join. Die Erklärungen in Power Query finde ich bei den Join-Typen gut. Nur die Formulierung der Datensatzzahlen („Die Auswahl schließt 990 von 1000 Zeilen der ersten Tabelle aus.“) irritiert mich nahezu jedes Mal.

Übrigens, innerhalb des Dialogs könnte ich auch eine Fuzzy-Search machen. Diese kann allerdings nicht so gut ausgewertet werden, wie mittels des AddIns. Wie dieses funktioniert, hat meine Kollegin Maria Drießen in ihrem Beitrag beschrieben.

Danach oben links einfach auf „Schließen & laden“ drücken – schon sind die Daten in einer neuen Excel-Tabelle.

Was ist jetzt mit mehreren Millionen Datensätzen?

Der Trick ist ganz einfach: Excel lädt die Daten erst, nachdem wir Power Query verlassen und veranlassen die Daten an einem Ort zu speichern. Statt einen Import in Excel zu veranlassen, können wir diese einfach ins Datenmodell laden und einzig die Verbindung hinterlegen.

Die Daten können jetzt mittels Pivot-Tabellen (eigentlich eher Power Pivot) ausgewertet werden. Einziger Wehmutstropfen (der aus meiner Sicht keiner ist), ist die Tatsache, dass die Daten nicht als normale Excel-Tabelle vorliegen. Die paar Millionen Zeilen muss man dann auf andere Art ausdrucken... Aggregieren, Filtern und Analysieren kann man sie ja mittels Pivot.

Fazit

Ich bin von Power Query begeistert und entdecke immer wieder neue Möglichkeiten innerhalb des Imports. Excel bleibt leider in vielen Fällen und Situationen das Tool der Wahl. Durch Power Query kann ich aber schneller und sauberer Arbeiten. Der Vorteil ist, dass die Vorgänge auch schnell wiederholbar sind. Ein einfacher Klick auf „Alle Aktualisieren“ bringt mir einen neuen Import in die Mappe. Vorlagen schreiben und effizienter Arbeiten... Was möchte man mehr?

In den nächsten Wochen und Monaten werden meine Kollegin Maria und ich noch einige weitere Features von Power Query vorstellen. Geben Sie uns gerne Feedback und Fragen bei LinkedIn und per Mail!