Das Ziel: schnell und unkompliziert einen Headcount Report erstellen. Und diesen mit möglichst wenig Aufwand immer wieder benutzen.
Heute holen wir uns die Quelldaten, prüfen sie auf mögliche Fehler und transformieren sie so, dass wir damit in der nächsten Folge den Report erstellen können.
Warnung
Dieser Teil ist recht technisch. Und deshalb für einige wahrscheinlich nicht wahnsinnig spannend. Es ist zwar kein detailliertes Excel-Tutorial, aber ich beschreibe grob, wie ich die Daten mit Power Query bearbeite.
Inhaltsverzeichnis
Was geschah bisher?
Der CEO möchte immer wieder wissen, wieviele Leute für uns arbeiten. Anstatt jedes Mal einen aufwändigen Ad hoc Report zu erstellen, werden wir einen Standard-Report sowie einen dazugehörigen Prozess kreieren.
Im 1. Teil haben wir dem CEO einen Vorschlag gemacht und mit ihm vereinbart, dass wir folgendes liefern werden:
- monatlicher Report (1 Tabelle, 2 Grafiken)
- Anzahl Mitarbeitende pro Monat und pro Abteilung
- pro Kalenderjahr (nicht rollend über die letzten 12 Monate
Im Sinne eines MVPs wird es also keine Informationen zu FTE, Fluktuation etc. geben. Und das «Mini-Dashboard» wird auch nicht interaktiv sein sondern als pdf-Datei verschickt.
Dies entspricht der 1. Phase unseres Projektes mit den folgenden Schritten:
- Schritt 1: Bedürfnisse und Situation abklären
- Schritt 2: Erarbeitung eines Vorschlags
- Schritt 3: Vereinbarung des Auftrags mit dem Kunden
Schritt 4: Daten sammeln und sichten (extract)
Woher bekommen wir die Daten?
Wir arbeiten mit der KML («Kleine Mitarbeiter-Liste»). Es handelt sich um eine Excel-Tabelle mit den wichtigsten und am meisten benutzten Mitarbeiterdaten.
Auch wenn nicht sehr fancy, diese Liste ist (hoffentlich) vollständig, korrekt und wird regelmässig aktualisiert. Ein weiterer Vorteil ist, dass wir direkt auf diese Datei zugreifen können. Wir müssen also nicht Daten bei jemandem bestellen, der sie zuerst exportieren und uns dann zur Verfügung stellen muss.
Welche Daten brauchen wir?
Im Sinne unseres MVPs brauchen wir hier nur zwei Informationen:
- Abteilung der Mitarbeiter
- in welchen Monaten die Mitarbeiter angestellt waren
Wir haben also alles, was wir brauchen. Soweit, so gut.
Daten einlesen
Wir öffnen eine neue Excel-Datei und holen die Informationen der KML mit Power Query rein. Da die KML keine intelligente Tabelle ist, müssen wir die Daten via Datenblatt holen.
Das war gemäss ETL auch schon der Extract-Schritt.
Daten sichten
Wir haben jetzt also Zugriff auf die benötigten Daten und können damit «spielen». Und mit dieser Methode besteht auch keine Gefahr, dass wir die Originaldaten verändern oder sogar kaputt machen.
Wie sehen also unsere Daten aus? Gibt es etwas, das uns gleich auffällt und möglicherweise zu Problemen führen könnte?
Power Query benutzt automatisch die 1. Zeile als Spaltenüberschrift und die verschiedenen Datentypen werden auch korrekt erkannt (z.B. Zahlen). Die Information zur Abteilung ist vorhanden. Zwar in Form von «Abteilung Team» bzw. Kostenstelle. Aber wir können etwas damit anfangen.
Weiter gibt es für jeden Monat eine Spalte mit dem jeweiligen Arbeitspensum. Das heisst also, jemand war in einem bestimmten Monat angestellt, wenn das Pensum grösser 0 ist. Auch hier nicht perfekt, aber wir können damit arbeiten.
Schritt 5: Daten bereinigen (transform)
Als nächstes wollen wir die Daten für den Report vorbereiten. Gleichzeitig wollen wir aber auch sicherstellen, dass die Daten nicht fehlerhaft sind.
Nicht benötigte Zeilen
Wir haben in der KML einige Zeilen nur mit Personalnummer.
Um zu verhindern, dass wir bei Neueintritten eine bereits benutzte Personalnummer verwenden, haben wir in der KML ein paar Nummern «auf Vorrat» erstellt. Das bringt uns hier aber nichts. Deshalb filtern wir diese Zeilen weg.
Falsche Abteilungskombinationen?
Wir brauchen für den Report nur die Abteilung, z.B. «Finance» oder «IT».
Leider geben wir in der KML jeweils eine Kombination aus Abteilung und Team ein, z.B. «Finance Controlling» oder «IT Support». Wir können aber nicht einfach das 1. Wort extrahieren (Abteilung), da der volle Name nicht immer aus «Abteilung Team» besteht, z.B. «Recruiting» anstatt «HR Recruiting» oder «Payroll», obwohl es (im Moment) zu Finance gehört.
Hinzu kommt, dass diese Information von Hand eingefügt wird anstatt auf eine Referenztabelle zuzugreifen. Deshalb besteht die Gefahr, dass etwas eingegeben wird, das bei uns gar nicht existiert (z.B. «IT Controlling»).
Zum Glück haben wir in der KML auf einem separaten Datenblatt eine «offizielle» Liste mit Abteilugen, Teams und den Kostenstellennamen («KST Name»).
Wir werden diese Tabelle bzw. das Datenblatt mit Power Query einlesen und eine kleine Kontrolle einbauen.
Kontrolle der Abteilungsnamen
Wir lesen die Liste der Kostenstellen aus der KML ein und speichern sie als Abfrage ab.
Als nächstes verbinden wir die Hauptabfrage und die Kostenstellenabfrage mit einem linken Anti-Join. Dadurch erhalten wir eine Liste mit allen Mitarbeitenden die, im Vergleich zur KML-Referenzliste, eine falsche Abteilung bzw. Kostenstelle haben.
Wir finden in der Tat Personen mit den Kostenstellen «HR Support» und «IT Controlling». Das korrigieren wir in der KML und führen in der Report-Datei eine Aktualisierung durch. Jetzt ist die Kontroll-Liste leer.
FTE-Spalten umwandeln und kontrollieren
Als nächstes entpivotieren wir die Spalten [FTE Jan» : «FTE Dez»]. Dadurch werden die 12 FTE-Kolonnen in zwei neue Spalten transformiert und wir nennen sie «FTE-Monat» und «FTE-Wert».
In einer separaten Abfrage kontrollieren wir, ob die einzelnen FTE-Informationen korrekt sind.
In der KML werden die Arbeitspensen als ganze Zahlen eingegeben, z.B. «100» für 100% oder «80» für 80%. Deshalb suchen wir nach Zahlen, die grösser als 0 aber kleiner oder gleich 1 sind. Denn dann wurde das Pensum wahrscheinlich als Prozentzahl eingegeben (z.B. 90% oder 0.9 anstatt 90). Dem müssten wir dann nachgehen.
In einer neuen Abfrage setzen wir die Bedingungen «FTE-Wert > 0» und «FTE-Wert <= 1». Wir laden diese Abfrage in eine Tabelle und finden ein paar Fehler.
Ein Vergleich mit der KML bestätigt:
- Beim Dominic wurde für März «100%» eingegeben.
- Und beim Ivo steht jeweils «0.9».
Wir korrigieren diese Angaben in der KML, aktualisieren die Report-Datei und nun ist auch diese Kontroll-Liste leer.
FTE- in Headcount-Informationen umwandeln
Jetzt verfügen wir über korrekte FTE-Informationen, aber eigentlich müssen wir nur wissen, ob jemand in einem bestimmten Monat angestellt war. Dazu erstellen wir in der Abfrage eine neue Spalte mit den Bedingungen:
- «1» falls FTE-Wert > 0
- sonst «0»
Headcount: zukünftige Informationen ausschliessen
Die KML enthält auch FTE-Informationen für zukünftige Monate. Wir wollen im Report aber nur Daten für die aktuelle Berichtsperiode berücksichtigen. Also z.B. für den März-Report keine Informationen von April und späteren Monaten.
Da bei uns alles etwas handgestrickt ist, benützen wir diese «quick & dirty»-Lösung: zuerst erstellen wir eine neue Tabelle mit den folgenden Spalten:
- FTE-Monat: entspricht den alten FTE-Spalten bzw. der Information unter «FTE-Monat» in der Hauptabfrage
- Monat: Monatsnummer 1-12
- Datum: letzter Tag des Monats, wenn sich der entsprechende Monat innerhalb der Berichtsperiode befindet.
Wie funktioniert die Spalte «Datum»?
Formel für «Datum»:
=WENN([@Monat]<=Fehler!$B$2,MONATSENDE(DATUM(Fehler!$B$3,[@Monat],1),0),"")
Im neuen Datenblatt «Fehler» (wir kommen in einer späteren Folge darauf zurück) geben wir die Monatsnummer und das Jahr des Berichtsmonats ein (z.B. «3» und «2021» für März 2021). Für Monate im oder vor dem Berichtsmonat wird in der Spalte «Datum» der letzte Tag des Monats angegeben (z.B. «31.03.21» für März). Bei späteren Monaten bleiben die entsprechenden Zellen leer.
Wir erstellen aus dieser Tabelle eine neue Abfrage und verbinden die Hauptabfrage und diese Monats-Abfrage mit einem linken äusseren Join. Dadurch haben wir alle Zeilen der Hauptabfrage, ergänzt durch das Datum (Monatsende) aus der Monatstabelle.
Für zukünftige Monate ist das Datum «null» und wir filtern diese Zeilen weg. Somit haben wir nur noch die Zeilen aus der Berichtsperiode.
Abteilungsnamen hinzufügen
Im Moment haben wir immer noch die komischen Abteilungskombinationen (bwz. Namen der Kostenstellen). Deshalb verbinden wir die Hauptabfrage mit der bereits existierenden Kostenstellenabfrage und aktivieren die Spalte mit den Abteilungsnamen.
Und was nun?
Nun verfügen wir über kontrollierte und bereinigte Daten. Weiter haben wir sie soweit «in Form gebracht», um damit einen Report zu erstellen.
Anders gesagt: wir haben die Informationen aus der Quelldatei (KML) extrahiert und transformiert. Um den ETL-Prozess abzuschliessen, laden wir die Daten in eine Tabelle.
Und im nächsten Teil werden wir dann endlich den Report erstellen.
0 Comments
Trackbacks/Pingbacks