Wie kann man in Power Query eigentlich Daten korrigieren? Wenn du Daten, wie falsche Kostenstellen oder fehlerhafte Artikelnummern vor der Auswertung bisher in Excel bereinigt hast, ist unser Errata Pattern für dich!
Warum Daten ändern?
In Excel kann man ganz einfach Daten ändern. Monatlich werden also Daten in Excel importiert. Entweder manuell oder über einen SVERWEIS werden dann Daten angepasst. Mal wird die Kostenstelle, mal die Niederlassung angepasst und dann noch die Nummer des Verkäufers. Im jeweiligen ERP-System (SAP, Navision, JD Edwards etc.) lassen sich die Buchungen ja nicht mehr ändern. Und da ist ja auch gut so! Prinzipiell gilt eben wie überall: “Shit in, Shit out”. In Excel konnte man das aber manuell bereinigen. Wenn du Power Query einführen willst, wirst du nun genau vor diese Aufgabe gestellt.
Ausgangsdaten
Als Beispiel verwenden wir eine fiktive Buchungstabelle. In jeder Zeile haben wir einen Schlüssel, die Kostenstelle, eine Mitarbeiternummer sowie den Wert der Buchung. Bei der Buchung mit ID 2 wurde die Kostenstelle 9999 eingegeben. Da sollte aber die Kostenstelle 4713 stehen. In Buchung mit ID 5 hat sich der Sachbearbeiter vertippt und einen Zahlendreher eingebaut. Eigentlich sollte hier auch 99089 stehen.
Daten korrigieren: Die Errata-Liste
Du kennst das sicherlich von Fachbüchern: Du kaufst ein frisch gedrucktes Buch und findest gleich hinten drin eine Errata-Liste mit Fehlern. Auch wenn wir kein Buch schreiben, verwenden wir zumindest das gleiche Prinzip: Wir legen eine Errata-Tabelle an, in der wir die korrekten Daten der Buchung eintragen. Um es einfach zu halten, legen wir eine Tabelle mit den gleichen Spalten an, wie in der Buchungstabelle. Dort tragen wir wir die richtigen Buchungsdaten ein. Natürlich nur für die Buchungen, die wir korrigieren möchten.
Die Idee
Von unseren Eingangsdaten Buchungen nehmen wir nur die Zeilen, die nicht in der Errata-Liste vorkommen. Dieser gefilterten Buchungsliste fügen wir die Zeilen der Errata-Liste an. Damit haben wir wieder gleich viele Buchungen – inklusive Korrekturen.
Vorgehen in Power Query
Als erstes laden wir beide Tabellen in Power Query. Dann erstellen wir eine neue Abfrage; und zwar eine Kombinationsabfrage. Abfragen als neu anfügen legt eine neue Abfrage an, in der die Zeilen zweier oder mehrerer Ausgangstabellen angefügt werden. Abfragen als neue Abfrage zusammenführen führt zwei Tabellen mit einer Bedingung zusammen. Du gibst bei einer Kombination eine linke (hier: obere) und eine rechte (hier: untere) Tabelle an. Die Verknüpfungsart (hier: Join-Art) bestimmst du über die untere Auswahl:
- Linker äußerer Join: Alle Zeilen der linken Tabelle werden übernommen und kombiniert mit der rechten, die zur linken passen
- Rechter äußerer Join: Alle Zeilen der rechten Tabelle werden übernommen und kombiniert mit der linken, die zur rechten passen
- Vollständiger äußerer Join: Es werden alle Zeilen beider Tabellen genommen. Die Felder, die in der jeweilig anderen Tabelle nicht vorhanden sind, bleiben dann einfach leer
- Linker Anti-Join: Die Zeilen werden aus der linken Tabelle genommen, wenn sie nicht in der rechten vorhanden sind
- Rechter Anti-Join: Die Zeilen werden aus der rechten genommen, wenn sie nicht in der linken vorkommen
Wann “passen” Zeilen von links und rechts zusammen, bzw. kommen in der einen vor nicht aber in der anderen? Dazu muss man Schlüsselfelder angeben. Diese Spalten markierst du oben und unten. In diesem Fall also nur die ID-Spalte. Du kannst aber auch mehrere Spalten markieren. Die ausgewählten Spalten werden geprüft und je nach gewählter "Join-Art" müssen diese entweder übereinstimmten oder eben nicht.
Linker Anti Join
Wir verwenden den Anti-Join. Wir wollen alle Buchungen, die in der Errata-Liste nicht vorkommen: In der Abfrage Merge1 finden wir jetzt nur Zeilen, die in der Errata-Liste nicht vorkommen. Die Spalte Errata können wir löschen (nein, nicht löschen: Wir wählen alle Spalten außer der Spalte Errata aus – lies unseren Blogbeitrag zum Löschen von Spalten).
Anfügen der Errata-Zeilen
Als letzten Schritt müssen wir jetzt die Zeilen aus der Merge1 Abfrage und der Errata-Tabelle zusammenführen. Das machen wir wieder über eine Kombinationsabfrage. Dieses Mal verwenden wir Abfragen als neu anfügen. Das Ergebnis ist der Input für unser Datenmodell.
Nur Verbindung erstellen
Die Ergebnisse der Abfragen Buchungen, Errata und Merge1 benötigen wir im Datenmodell nicht. Im Blog-Beitrag über Verweise, Gruppen etc. zeigen wir dir, wir man Abfragen nur weiter in Excel oder ins Datenmodell laden muss und wie man sie in Gruppen strukturieren kann.
Vorteil dieser Vorgehensweise
Oft haben wir gesehen, dass in mehreren Excel-Dateien, gerne in ausgeblendeten Tabellenblättern, Ersetzungen via Formeln, Makros oder ähnliches durchgeführt werden. Schnell verliert man den Überblick. Über eine Errata-Liste, wie hier vorgestellt, hat man einen definierten Ort, an dem man Ersetzungen nachvollziehen, ändern und ergänzen kann. Da freut sich nicht nur ein Wirtschaftsprüfer.
Ausblick
In diesem Beispiel hat die Errata-Liste genau den selben Aufbau, wie die Quelldaten. Damit kann man vollständige Zeilen ersetzen. Natürlich kann man sich auch vorstellen, dass man in einer Errata-Liste nur Spalten aufnimmt, die man potentiell korrigieren möchte und dann nur die Spalten füllt, die tatsächlich korrigiert werden sollen. So kann man beim Einlesen prüfen, ob es Zeilen in der Errata-Liste gibt und ob es für Spalten andere Werte gibt, als im Originalsatz. Der Grundsatz bleibt aber der Gleiche: Änderungen von Daten explizit und nachvollziehbar zu hinterlegen.
Unterstützung gewünscht?
Wir bieten Power Pivot, Power Query und Power BI Schulungen an. Vom Anfänger bis zum BI-Consultant, der neue Werkzeuge kennen lernen will. Wir unterstützen in Workshops mit deinen Auswertungen und deinen Daten. Oder wir tauschen die Rollen, und wir implementieren die Reports nach deinen Wünschen selbst. Hier findest du mehr zu unseren Leistungen.
Oder nimm direkt Kontakt mit uns auf!
Kommentar hinterlassen