Für Controlling und Reporting sollen mehrere Excel-Dateien in eine einzige Excel-Datei importiert werden mittels Power Query. Der Einsatz von Power Query ist ein nützliches Werkzeug für Dashboards, Controlling und Reporting. In diesem Artikel wollen wir kurz eingehen, wie einfach der Einsatz von PowerQuery sein kann.
Hintergrund und Ausgangsproblem im Controlling
Für Controlling und Reporting werden häufig Master-Excel-Dateien verwendet, um Planungszahlen oder Budget-Zahlen zu kontrollieren. Die Master-Excel Datei wir dann den Fachabteilungen (z.B. Personal, Finanzen oder Rechnungswesen) zur Verfügung gestellt, um diese zu bearbeiten.
Anschließend werden die Excel-Dateien wieder an die Controlling-Abteilung zurück gesendet und diese muss die Daten wieder in einer einzigen großen Excel-Datei zusammenfassen.
Lösung mittels Power Query
Vor Excel 2016 wurde dieser ETL-Prozess (Extract, Transform, Load) primär mit VBA-Skripten gelöst. Mit Power Query ist dies einfacher zu gestalten.
Zudem unterstützt Power Query nicht nur Excel-Dateien sondern auch CSV-Dateien und Datenbank-Systeme, die eingebunden werden können.
In diesem kleinen Tutorial werden wir uns jedoch mit dem Importieren von mehreren Excel-Arbeitsmappe und einigen grundlegenden Datenbereinigungstransformationen befassen um das Prinzip zu erkennen.
Wo finde ich Microsoft Power Query?
Power Query ist ab Excel 2016 fest integriert. Für Excel 2010 Professional Plus und für Excel 2013 kann dies nachinstalliert werden unter:
https://www.microsoft.com/en-us/download/details.aspx?id=39379
Im Menüband unter: Daten -> Daten abrufen
Findet man den Punkt Power Query Editor starten.
Wo findet man PowerQuery in Excel
Mehrere Excel Dateien importieren mittels Power Query
Da wir mehrere Excel Dateien mittels Power Query importieren wollen, werden wir gleich folgenden Schritt ausführen:
Daten -> Datenabrufen -> Aus Datei und -> Aus Ordner
Anschließend wählen Sie Ihren Pfad aus, wo alle Excel-Dateien liegen, die Sie in Power Query importieren wollen.
Der ausgewählte Pfad kann auch zu einem späteren Zeitpunkt umgestellt werden.
Mit Click auf den OK Button sehen Sie alle Excel Dateien, die zusammengefasst werden können.
Da wir die Inhalte (Power Query: Content) je Tabellen-Arbeitsmappe inhaltlich haben wollen, muss für diesen ETL-Prozess eine benutzerdefinierte Spalte eingefügt werden unter:
Menü-Leiste „Spalte hinzufügen“ -> Benutzerdefinierte Spalte
Jetzt öffnet sich ein kleines Popup-Fenster in Power Query. Hier können Sie einen individuellen Spalten-Namen vergeben. In unserem Beispiel benennen wir die Spalte in „Inhalt-Excel-Tabelle“ um.
In der benutzerdefinierten Spaltenformel geben Sie folgenden Befehl ein:
each Excel.Workbook([Content])
und klicken auf den OK Button.
Jetzt sehen Sie die Benutzerdefinierte Spalte „Inhalt-Excel-Tabelle“ und gleichzeitig wird in Power Query protokolliert, dass ein weiterer Anwendungsschritt (Abfrageeinstellungen) hinzugefügt worden ist.
Mit diesem Historienverlauf können Sie Ihre Einstellung nochmals prüfen und ggf. auch löschen.
Klicken Sie jetzt auf das kleine Symbol in der Spalte, um alle Spalten-Inhalte und Werte aus der Excel Tabelle anzusehen und anschließend auf OK.
Es werden in Power Query automatisch neue Felder hinzugefügt.
In dem Feld Table nochmals auf das kleine Symbol klicken um weiteren Spalten-Inhalte anzuzeigen.
Nun werden die ganzen Inhalte von der Excel-Tabellenblatt angezeigt inkl. der Dateinamen.
Die meisten Inhalte sind direkt mit der Filter-Funktion von Power Query zu bekommen.
Klicken Sie auf die gewünschte Spalte und setzen Sie hier Ihren Filter ein, so wie Sie es von den klassischen Excel-Tabellen kennen. In unserem Beispiel filtern wir die Kostenstelle nach STH und es werden nur diese Daten später importiert.
Und drücken Sie auf dem Menü-Band „Home“ auf den Button „Schließen & laden“.
Jetzt haben Sie mehrere Excel Dateien direkt mittels Power Query importiert und können weiteren Berichte aufsetzen.
In unserem Beispiel waren es drei Excel-Dateien für das FI-Reporting und HC-Controlling
Ladevorgang in Power Query beschleunigen.
Je nach Dateigröße und Daten empfehlen wir den Datentyp je Spalte direkt in PowerQuery umzuwandeln. Dies beschleunigt den ELT-Prozess erheblich und die Daten müssen später in Excel nicht nochmals umgewandelt werden (z.B. Text in Datum oder Text in Zahl etc.).
Erweiterter Editor in Power Query
Ab und zu rentiert sich ein Blick in den Erweiterten Editor.
Hier werden die historischen Schritte alle aufgezeigt und in der Programmiersprache M dargestellt.
Sie können hier z.B. den Import-Pfad einfacher umstellen oder bestimmte Filterkriterien schneller bearbeiten.
Sie benötigen Unterstützung bei Ihren ETL-Prozessen oder suchen eine alternative für Ihr Controlling und Reporting Portal, dann setzen Sie auf unsere Erfahrungen und Experten im Bereich Datenbanken, Reporting und Controlling-Lösungen für Rechnungswesen, Personal-Controlling und FI-Reporting