Fremde Datensets prüfen und Duplikate entfernen

Sie sollten einem Datenset, das Sie bekommen, prinzipiell erst einmal nicht trauen, denn Sie wissen nicht, wie kompetent es tatsächlich erstellt wurde. Vielleicht hat die Person, die das Datenset erstellt hat, einen falschen JOIN verwendet, oder vielleicht hat sie die Anforderungen nicht verstanden. Sehen Sie sich also die Datensets, die Sie von anderen erhalten, stets genauer an. Häufig kommt es zu Problemen, wenn Sie später Berechnungen mit den Daten durchführen, weil Daten schlichtweg fehlen. Okay, sehen wir uns die Auftragsdaten hier an. Ganz am Ende rechts steht eine Spalte Gesamtsumme. Hier sollten Sie prüfen, ob die Summe so korrekt ist und ob wirklich alle Bestell-Elemente enthalten sind. Sehen Sie sich dazu die Abfrage an. Wählen Sie Daten > Abfrage und Verbindungen, dann klicken Sie mit der rechten Maustaste in die Abfrage und wählen Bearbeiten. Wenn Sie nach rechts blättern, dann sehen Sie die Spalten Stückpreis, Menge, Rabatt, Steuer, Einzelsumme und Sie wollen nun prüfen, ob die Gesamtsumme zu diesen Beträgen passt. Dazu brauchen Sie eine Hilfsspalte, in der Sie eine Testberechnung durchführen. Das geht ganz einfach, wählen Sie oben im Menü Spalte hinzufügen > Benutzerdefinierte Spalte. Die neue Spalte soll Testberechnung heißen und die Formel lautet: Stückpreis mal Menge minus Rabatt plus die Steuer. Die Formel enthält keine Syntaxfehler, bestätigen Sie sie also mit OK. Dann weisen Sie der neuen Spalte noch das Währungsformat zu. Okay, in der ersten Zeile ergibt die Einzelsumme plus die Steuer einen Betrag von 8,59. Das entspricht der Zahl in der Testberechnung, aber nicht der Gesamtsumme. Die Gesamtsumme beträgt 78,76. Woher kommt diese Zahl? Sie müssen sich die Daten also noch genauer ansehen. Klicken Sie auf der Registerkarte Start auf die Schaltfläche "Schließen & laden", um die Daten wieder in Excel anzuzeigen. Schließen Sie den Aufgabenbereich, damit Sie mehr Platz auf dem Bildschirm haben und blättern Sie in der Tabelle nach rechts. Okay, die Einzelsumme plus die Steuer ergibt den Betrag in der Spalte Testberechnung. Und dieser Betrag entspricht nicht der Gesamtsumme. Sie müssen also noch tiefer in Ihre Daten hineinschauen. Sehen Sie sich die Originaldaten im Arbeitsblatt Auftragsdaten an. Es gibt eine Spalte mit Auftragszeilen-Nummern und links gibt es auch noch eine Spalte mit Auftragsnummern. Vielleicht ist das die Ursache für das Problem. Nämlich Auftragsnummern, die mehrfach vorkommen und mit unterschiedlichen Zeilennummern den Gesamtbetrag ergeben. Sie müssen also herausfinden, ob es bei den Auftragsnummern Duplikate gibt. Testen Sie das am besten mit der bedingten Formatierung. Die Spalte F ist bereits markiert, dann wählen Sie die Registerkarte Start, den Befehl "Bedingte Formatierung", den Befehl "Regeln zum Hervorheben von Zellen" und dann hier unten "Doppelte Werte". Und schon werden alle möglichen Duplikate rot formatiert. Und in Zeile 6 und 7 kann man ganz klar sehen, dass es sich hier um dieselbe Auftragsnummer handelt. Klicken Sie auf Abbrechen. Wechseln Sie zurück zu den bereinigten Daten und blenden Sie die Abfrage ein mit Daten > Abfragen und Verbindungen und dann klicken Sie mit der rechten Maustaste auf die Abfrage und wählen Bearbeiten. Wenn unsere Vermutungen stimmen, dann zeigt dieser Stand also die einzelnen Bestellzeilen an. Benennen Sie die Abfrage um. Sie soll BestellZeilen heißen. Dann erstellen Sie ein Duplikat dieser Abfrage, klicken Sie mit der rechten Maustaste in den Namen und wählen Duplizieren. Das Duplikat enthält genau dieselben Daten mit genau denselben Schritten, aber es besteht keine Verbindung zum Original. D.h., wenn Sie in der Tabelle Bestellzeilen etwas ändern, dann werden die Änderungen nicht im Duplikat übernommen. Benennen Sie auch die zweite Abfrage um. Sie soll AuftragZeilen heißen. Wenn Sie hier die Duplikate entfernen, erhalten Sie nämlich eine Liste mit eindeutigen Auftragszeilen, also mit eindeutig individuellen Aufträgen. Aber zuerst müssen Sie alle Spalten entfernen, die zu einer unerwünschten Eindeutigkeit in den Auftragszeilen führen können. Wählen Sie auf der Registerkarte Start in der Gruppe "Spalten verwalten" die Schaltfläche "Spalten auswählen". Deaktivieren Sie die ProduktID, denn es kann für ein und dieselbe Auftragsnummer verschiedene ProduktIDs geben. Diese Zeilen würden dann nicht als Duplikate erkannt werden. Dasselbe gilt auch für die Auftragszeilennummer. Auch hier kann es pro Auftragsnummer verschiedene Auftragszeilennummern geben. Und auch Stückpreis, Menge, Rabatt, Steuer und Einzelsummen sind individuelle Werte und die Spalte Testberechnung brauchen Sie auch nicht mehr. Bestätigen Sie mit OK. Jetzt müssen Sie die Duplikate entfernen. Klicken Sie dazu vor der ersten Spalte auf das Tabellen-Symbol und wählen Sie den Befehl "Duplikate entfernen". Dann klicken Sie auf "Schließen & laden". Es gibt nun also zwei Abfragen, die Abfrage BestellZeilen mit 60.351 Zeilen und die Abfrage AuftragZeilen mit 27.639 Zeilen. Wenn Sie die Abfrage AuftragZeilen markieren, dann werden automatisch die Daten des Arbeitsblattes AuftragZeilen angezeigt. Und wenn Sie auf BestellZeilen klicken, werden die bereinigten Daten angezeigt und dieses Arbeitsblatt sollten Sie noch umbenennen. Und zwar soll es BestellZeilen heißen. Wenn Sie also hier ein Blatt wählen, wird die entsprechende Abfrage rechts angezeigt. So können Sie die Daten und die Abfragen eindeutig zuordnen. Es gibt verschiedene Wege zum Entfernen von Duplikaten. Was die Power Query-Methode aber so wertvoll macht, ist die Aufzeichnung der Schritte. Wenn Sie die Daten aktualisieren, werden auch diese Schritte automatisch aktualisiert. Wenn Sie stattdessen die Duplikate erst manuell entfernen, um dann die Daten zu bearbeiten, dann leidet die Datenqualität. Das automatische Entfernen von Duplikaten trägt also zu einem qualitativ hochwertigen Datenset bei.