Machst Du manchmal Fehler bei der manuellen Datenpflege in Excel? Ich auf jeden Fall. Aus diesem Grunde schreibe ich mir gerne Hilfstools zur Datenprüfung mit Power Query, die mir solche Fehler schnell aufzeigen. So kann ich sie korrigieren, bevor Schaden entsteht. In diesem ersten von zwei Teilen zeige ich Dir, wie man die Ist- und Solldaten so aufbereitet, dass es im Nachgang, also im zweiten Teil, ein Leichtes wird, die Fehlerermittlung vorzunehmen.

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

Der Sachverhalt

Ich habe es in der Praxis häufig erlebt, dass beispielsweise Projekte händisch in Exceltabellen gepflegt werden. Der folgende Screenshot zeigt eine solche Tabelle. Hier werden Projekte mit ihrem entsprechenden Projektleiter gepflegt. Die Projektleiter begleiten ein solches Projekt jedoch nicht immer über die gesamte Laufzeit hinweg. Aus diesem Grund wird in den Spalten Start und Ende vermerkt, wann der entsprechende Projektleiter für dieses Projekt veranwortlich (Start) wurde und wann er die Verantwortlichkeit ggf. wieder abgegeben hat (Ende).

Die Problemstellung

Problematisch wird es, wenn ich die Start- und Endedaten nicht korrekt pflege. Hierbei kann es zu drei Fällen kommen:

1. Für ein Projekt wird lückenhaft gepflegt, so dass wie im blau hinterlegten Beispiel des folgenden Screenshots, bei Projekt P002 der Zeitraum vom 02.04. – 04.04.2017 fehlt. Für diese Tage gibt es laut Tabelle keinen Verantwortlichen.
2. Für ein Projekt wird redundant gepflegt, so dass wie in den rot hinterlegten Beispielen des folgenden Screenshots, bei Projekt P001 der 23. und 24.07.2017 doppelt gepflegt ist und zusätzlich bei Projekt P002 der 28.04 und 01.05.2017 doppelt gepflegt wurde. Für diese Tage gibt es laut Tabelle mehr als einen Verantwortlichen.
3. Die Fälle 1 und 2 treten in Kombination auf, wie für Projekt P002 ersichtlich.

Um solche Fehler der Dateneingabe ausfindig zu machen, stelle ich Dir eine Lösung vor, die Dir die fehlerhaft gepflegten Projekte, samt fehlerhafter Datumswerte wie folgt ausweist:

Auf diese Weise kannst Du entstandene Fehler sehr schnell ausfindig machen und anschließend beheben. Wie ich das mit Power Query umgesetzt habe, zeige ich Dir jetzt.

Die Lösung

Der Weg zur Lösung führt über mehrere Schritte, die ich Dir jetzt beschreibe.

1. Datenimport in Power Query

Die in meinem Excelblatt vorhandenen Daten müssen zunächst in Power Query importiert werden. Während sich die aktive Zelle innerhalb der gewünschten Tabelle befindet, drücke ich hierzu auf die Schaltfläche Daten → Aus Tabelle/Bereich, um die Tabelle in Power Query zu laden.

2. Entfernen des Projektleiters

Da ich je Projekt prüfen möchte, ob bestimmte Tage gar nicht, oder gar mehrfach gepflegt wurden, ist für diese Untersuchung der Projektleiter überflüssig und kann daher entfernt werden. Als nächstes ermittle ich die tatsächlich gepflegten Zeiträume.

3. Ermittlung der gepflegten Daten

Weil ich für jedes Projekt den gepflegten Zeitraum auf Richtigkeit prüfen will, muss ich den gepflegten Zeitraum auch je Projekt gruppieren: Und dies am besten in einer Liste! Das Ergebnis sieht dann wie folgt aus:

Je Projekt werden alle gepflegten Daten in einer Liste gespeichert. Dies habe ich wie folgt bewerkstelligt:

Datumsbereiche zeilenweise in eine Liste einlesen

Zunächst lese ich in der bestehenden Tabelle zeilenweise die Datumsbereiche in eine Liste ein. Die hierfür notwendige Formel kannst Du dem folgenden Screenshot entnehmen:

Die hier verwendete Funktion List.Dates() benötigt 3 Argumente:

• start: Ein Startdatum,
• count: Die Anzahl an steps, die von start aus gezählt werden soll (10 Tage → count = 10),
• step: Die Art der Laufzeit:
  • #duration(1,0,0,0) → Tag,
  • #duration(0,1,0,0) → Stunde,
  • #duration(0,0,1,0) → Minute,
  • #duration(0,0,0,1) → Sekunde

Achtung: In Kombination mit List.Dates() ist nur die Nutzung von #duration(1,0,0,0) möglich, da diese nur Tage kennt und keine kleinere Zeiteinheiten. Um die Schritte auf Stunden, Minuten und Sekunden zu verkleiner, ist die Nutzung der Funktion List.DateTimes() notwendig.

Um count in Tagen zu erhalten, ermittle ich die Dauer der Tage zwischen Start und Ende mit Hilfe der geschachtelten Funktionen Duration.Days(Durations.From([Ende] - [Start])) +1. Die FunktionDuration.From() ermittelt hierbei die generelle Dauer zwischen Start und Ende, während Duration.Days() diese in Tage umrechnet.

Löschen der Spalten [Start] und [Ende]

Da diese beiden Spalten nun in der erstellten Liste abgebildet sind, können sie entfernt werden. Das Ergebnis sieht wie folgt aus:

Gruppieren der Zeiträume je Projekt

Ich möchte nun erreichen, dass für jedes Projekt nur noch eine einzige Zeile besteht. Hierfür nutze ich die Schaltfläche Gruppieren nach:

Ich gruppiere nach der Spalte Projekt und vergebe den neuen Spaltennamen Listen. Nun sind zwei Besonderheiten zu beachten:

1. Aggregationstyp: Unter Vorgang wähle ich aus, dass Alle Zeilen beibehalten werden sollen. Hier wird im Normalfall die Aggregationstyp (Summe, Min, Max, etc.) ausgewählt, aber ich kann auch alle Zeilen behalten und diese in einer Liste ablegen. Somit wird kein neuer Wert generiert, sondern die Werte bleiben in einer Liste erhalten.
2. Spaltenauswahl: Wenn ich unter Vorgang Alle Zeilen ausgewählt habe, dann kann ich unter Spalte leider keine Auswahl der gewünschten Spalte mehr treffen. Deshalb ist das Rückgabeergebnis meiner Spalte Liste auch jeweils eine Table (wie im Screenshot zu sehen). Doch nur, weil es nicht über die Nutzeroberfläche geht, heißt das nicht, dass die Sprache es nicht doch kann. Ich löse das Problem, indem ich wie folgt manuell in die Formel eingreife:

Das Resultat ist eine Liste von Listen mit den jeweiligen Datumsbereichen pro Projekt. Diese Listen möchte ich jetzt für jedes Projekt in einer Liste zusammenführen.

Zusammenführen von Listen in einer Liste pro Projekt

Um die Einzellisten je Projekt zu einer Liste zusammenzuführen, erzeuge ich eine neue Spalte, unter Nutzung der Formel List.Combine():

List.Combine() fügt alle übergebenen Listen zu einer Liste zusammen. Dadurch habe ich nun je Projekt eine Liste mit Datumswerten vorliegen, wie sie in Excel manuell gepflegt wurden:

Damit habe ich die Ermittlung der Istdaten abgeschlossen. Um zu sehen, ob die Daten korrekt gepflegt wurden, ermittle ich nun die Sollwerte, mit denen ich im Anschluss den Abgleich der Istwerte vornehmen werde.

4. Ermittlung der Sollwerte

Die Sollwerte können sehr einfach ermittelt werden. Ich weiß, dass vom ersten erfassten Tag des Projektes, bis zum letzten Tag, jeder einzelne Tag genau einmal vorkommen muss. Sollte bei der Erfassung natürlich genau der Start- oder Endetag nicht erfasst worden sein, kann mein Tool dieses Problem nicht beheben. Um die Sollwerte ebenfalls je Projekt in einer Liste auszugeben, füge ich eine neue Spalte hinzu und verwende dazu die folgende Formel:

Diese Formel erzeugt eine Liste, die vom Starttag (ermittelt über List.Min([Istdaten])) bis zum Endetag des jeweiligen Projektes, jeden einzelnen Tag genau einmal beinhaltet.

Damit sind nun alle Daten so aufbereitet, dass die Fehlerermittlung beginnen kann. Doch dies wird Bestandteil des nächsten Teils dieses zweiteiligen Beitrags. 

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 🙂