Microsoft Power Query ist ein «pre-processor», welcher Daten bereinigt und formt, bevor diese in Power Pivot importiert werden. Als integraler Bestandteil der Power BI Familie Office 365 kann die Technologie aber auch als eigenständiges Tool zum Einsatz kommen. Dabei lassen sich bestehende Prozesse optimieren und repetitive Aufgaben automatisieren. Dies soll in diesem Blog anhand eines konkreten Fallbeispiels aufgezeigt werden.
Ein multinationales Unternehmen führt ein globales FTE-Reporting [1] ein, welches Ist- und rollierende Zukunftsdaten ihrer Mitarbeiter nach Kostenstellen und anderen Parametern (z.B. temporär vs. Festanstellung etc.) beinhalten soll. Da die Tochtergesellschaften unterschiedliche ERP-Systeme haben, ist eine Integration von lokalen HR-Daten in einer IT-gesteuerten BI-Lösung mit einem erhöhten Zeit- und Ressourcen-Einsatz verbunden. Dies kann kurzfristig nicht realisiert werden.
Globale Vernetzung
Der Group Controller hat die Aufgabe wie folgt gelöst: Er erstellt ein Excel-Template mit Mitarbeiter-bezogenen Kopfinformationen. Neben dem «Header» fügt er für x Monat-Jahr-Kombinationen je eine Spalte ein. Hier werden die Bewegungsdaten eingetragen. Der lokale Controller füllt die Informationen ein und sendet die Datei einmal pro Monat an die Gruppe. Der Group Controller kopiert den Datensatz in ein Tabellenblatt und aggregiert ihn mittels Formelabgleich in ein Hauptblatt. Die Totalen gleicht er mit einem Konsolidierungstool ab (z.B. Cognos Controller, Tagetik oder SAP BOFC).
Verbinden – Transformieren – Kombinieren – Laden
Der Excel Pro-Anwender sieht eine Möglichkeit, den Prozess zu optimieren und repetitive Aufgaben zu automatisieren: Für jede Tochtergesellschaft wird ein Ordner angelegt. Zugriff bekommen der Group- und der lokale Controller. Letzterer pflegt die Kopf- und Bewegungsdaten der lokalen Mitarbeiter und/oder aktualisiert das Template bei Zu- und Abgängen.
Anschliessend erstellt der Group Controller ein zentrales Excel-File. Er nutzt die Power Query-Technologie, um eine Datenbank aufzubauen. Nach der Verbindungsherstellung (A) zu der lokalen Quelldatei, erfolgt eine Datentransformation (B) in drei Hauptschritten:
- Erste Zeile als Kopfzeile verwenden & Format «Text» setzen.
- Alle Spalten Monat-Jahr «entpivotieren» (d.h. pro Spalte wird eine Zeile kreiert, die Header-Information automatisch dupliziert) & Format «Datum» setzen.
- Mit «Zusammenführen» Kostenstellen-spezifische Informationen hinzufügen (z.B. Bezeichnung, Funktion, Geschäftsbereich etc.).
Das Skript kann er nun für jeden Standort duplizieren und den Dateipfad im Quellverzeichnis anpassen. Im letzten Schritt werden die einzelnen Verbindungen mit dem «Anfügen»-Befehl (C) untereinandergelegt. Als sichtbarer Output wird der Datensatz als «intelligente Tabelle» in das Tabellenblatt geladen (D).
Der Initialaufwand ist schnell wettgemacht
Der Einsatz von Power Query hat unter anderem folgende Vorteilen für den Group Controller: Erstens die Zeitersparnis. Die Datenbank lässt sich mittels Drückens des «Aktualisieren»-Befehls schnell aufdatieren. Die Aktualisierung benötigt nur ein paar Sekunden. Zweitens kann die Datenbank periodisch abgespeichert und mit einem Datums- und Versionsstempel versehen werden. Dadurch können neben Ist- und rollierenden Zukunfts- auch Vergangenheits- und Budgetdaten abgespeichert werden. Drittens lässt sich die Datenbank in bestehenden Anwendungen und Reports einsetzen, da sich der Detaillierungsgrad gegenüber einer Konsolidierungssoftware stark erhöht. Zum Beispiel kann der Kostenstellenbericht mit der FTE-Entwicklung erweitert werden. Mit der Zentralisierung der Daten auf Gruppenstufe wird die Information schliesslich als Single Source of Truth [SOOT] bereitgestellt. Dies ist vorteilhaft in der Zusammenarbeit mit den lokalen Controllern und erhöht die Akzeptanz auf Gruppenstufe.
Nachteilig ist, dass in der Struktur der lokalen Datei keine Veränderungen vorgenommen werden dürfen. Das Hinzufügen/Entfernen von Spalten oder Umbenennen der Spaltentitel führt bei der Ausführung der Datentransformation unweigerlich zu Problemen. Des Weiteren ist der Aufbau einer zentrale Datenbanklösung zeitintensiv und erfordert eine vorgängige Planung. Der Initialaufwand wird aber mit jedem Aktualisieren der Datenbank weiter reduziert [Skaleneffekt].
Quellen
Collie, R. & Singh, A. (2016). Power Pivot and Power BI. The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016. Chicago: Independent Publishers Group.
Microsoft. (ohne Datum). Informationen zu Power Query in Excel. Excel für Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010. Zugriff am 10. April 2021 auf https://support.microsoft.com/de-de/office/informationen-zu-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a
Unsplash (11.05.2020). Glass Orb with Pattern. Zugriff am 30. April 2021 auf https://unsplash.com/photos/qDG7XKJLKbs
[1] FTE steht für Full-Time Equivalent und wird in Unternehmen gerne verwendet, um die effektive Arbeitsleistung sichtbar zu machen. Ein FTE-Wert von 0.8 steht demnach für eine 80%-Anstellung.
