Power Query: Das Errata Pattern

    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.

    fiktive Buchungstabelle

    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.

    Errata Liste

    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.

    Kombinationsabfrage in Power Query erstellen

    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.

    Dialog der Kombinationsbfrage mit Auswahl der Join Strategie

    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:

    Buchungen nach Anti-Join

    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.

    Dialog der Anfügeabfrage in Power Query

    Das Ergebnis ist der Input für unser Datenmodell.

    Ergebnis des Errata-Petterns

    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.

    Interessiert? Dann schreib uns eine E-Mail oder ruf uns direkt an!

    Kategorien: Artikel, Power BI, Power Pivot, Power Query, Tipps
    Holger Gubbels

    Holger Gubbels ist Spezialist für alles, was mit Business Intelligence zu tun hat. Der Diplom-Informatiker strukturiert und löst als Data Scientist und Berater der mogular GmbH tagtäglich komplexe Problemstellungen rund um die Datenanalyse. Als Dozent der Hochschule für Technik Stuttgart ist er ein ausgewiesener Experte seines Fachs und gewohnt, Inhalte einfach zu erläutern. Genau das macht er auch hier in seinen Beiträgen für das PowerPivotInsights Blog. Zusätzlich ist er Autor für die Computerwoche und den Springer-Verlag. Sie erreichen ihn unter hg@mogular.com

    Antwort schreiben

    Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.