Dies ist der zweite Teil meines Beitrags über die Datenprüfung zu lückenlos gepflegten Zeiträumen. Falls Du den ersten Teil noch nicht gelesen haben solltest, solltest Du dies jetzt tun, denn dieser Teil schließt nahtlos an den ersten Teil an. Im aktuellen Beitrag komplettiere ich alle notwendigen Schritte, um eine automatisierte Prüfung von in Excel befindlichen Daten vornehmen zu können – mittels Power Query.

Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!

 5. Die Fehlerermittlung

Die Fehlerermittlung teilt sich in zwei Teile: Die Ermittlung fehlender Datumseinträge und die Ermittlung mehrfach gepflegter Datumseinträge. Ich beginne mit fehlenden Datumswerten.

Welches Datum fehlt?

Für die Ermittlung fehlender Werte erzeuge ich eine neue Spalte und nutze dann die folgende Funktion:

List.Difference() benötigt als Parameter zwei Listen und prüft hierbei, welche Werte der ersten Liste (hier die Solldaten) in der zweiten Liste (die Istdaten) fehlen. Also genau das, was ich benötige. Das Ergebnis sieht dann wie folgt aus:

Für das Projekt P002 wurden die Tage 02.04.-04.04.2016 nicht gepflegt und diese Werte werden nun in der entsprechenden Liste ausgewiesen! Das erste Problem wäre also erledigt. Doch wie sieht es jetzt mit den mehrfach gepflegten Werten aus?

Welches Datum wurde mehrfach gepflegt?

Um die mehrfach gepflegten Werte ausfindig zu machen, benutze ich dieselbe Funktion List.Difference(), doch jetzt füge ich die Parameter in umgekehrter Reihenfolge ein:

List.Difference() berücksichtigt auch Duplikate als Unterschied zwischen beiden Listen. Da in den Solldaten jeder einzelne Tag exakt einmal vorkommt, ist jeder Tag, der in den Istdaten öfter vorkommt als in der Solldaten, automatisch ein mehrfach gepflegter Tag. So kann ich beispielsweise ermitteln, dass für das Projekt P001 der 23.07 und 24.07.2017 in den Istdaten mehrfach enthalten ist:

Damit habe ich alle notwendigen Kalkulationen zur Fehlerermittlung vorgenommen. Jetzt geht es darum, die Daten so zu extrahieren, dass man auch mit ihnen arbeiten kann.

6. Datenextraktion

Zu Beginn der Datenextraktion lösche ich die nicht mehr benötigten Spalten Istdaten und Solldaten. Anschließend füge ich die neue Spalte KonvertiereListenInTabelle, unter Verwendung der M-Funktion Table.FromColumns() hinzu:

Diese Funktion macht aus einer Liste von Listen (ja, eine Liste kann auch aus Listen bestehen) eine Tabelle. Diese Liste von Listen übergebe ich als erstes Argument ((1) im Screenshot). Da die Projekte (im Gegensatz zu den Spalten Wert fehlt und Wert ist mehrfach vorhanden) nicht in Form einer Liste vorliegen, dies für de Funktion Table.FromColumns() jedoch notwendig ist, fasse ich diese in geschweifte Klammern ein und konvertiere sie somit in eine Liste. Als zweites Argument der Funktion gebe ich die Spaltennamen der zukünftigen Tabelle mit ((2) im Screenshot). Nach dem Löschen aller überflüssigen Spalten und dem Extrahieren der neu kalkulierten Spalte KonvertiereListenInTabelle, sieht das Ergebnis dann wie folgt aus:

7. Zum Abschluss ein wenig Kosmetik

An dieser Stelle angekommen, möchte ich noch zwei kosmetische Änderungen vornehmen:

• Die Beseitigung der NULL-Werte in der Spalte Projekt,
• Die Entfernung der Zeilen, in denen kein Datum für Wert fehlt und Wert mehrfach gepflegt eingetragen wurde.

Ein vollständiges Bild der Projekte erhalte ich, indem ich die Spalte Projekt auswähle und die Schaltfläche Ausführen → Nach unten betätige:

Die Datensätze, die in beiden Datumsspalten NULL-Werte aufweisen, entferne ich dann abschließend wie folgt:

Ich aktiviere den Spaltenfilter der Spalte Wert fehlt und wähle die erweiterten Filteroptionen. Mit den im Screenshot ersichtlichen Einstellungen bleiben nur diejenigen Zeilen der Tabelle übrig, bei denen wenigstens eine der beiden Datumsspalten einen Wert ungleich NULL enthält.

Damit ist die Lösung fertig und ich habe stets den Überblick über fehlerhaft gepflegte Daten. Lasse ich mir diese Tabelle in Excel wiedergeben, sieht das ganze wie folgt aus:

Ich hoffe, diese Lösung regt Dich zu eigenen Lösungen an und hilft Dir in Deinem Arbeitsalltag weiter.

Bis zum nächsten Mal und denk bitte dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de

Viele Grüße aus Hamburg,

Lars

Ich schreibe meine Beiträge für Dich, den Leser. Bitte schenke mir eine Minute Deiner Zeit und bewerte die folgenden Kategorien, um mir zu helfen meine Beiträge so gut wie möglich zu schreiben. Danke 🙂