Mehrere Excel-Dateien importieren mittels Power Query

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 finde ich Microsoft Power Query in Excel

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

mehrere Dateien importieren in Power Query
mehrere Excel Dateien imporiteren im PowerQuery

Anschließend wählen Sie Ihren Pfad aus, wo alle Excel-Dateien liegen, die Sie in Power Query importieren wollen.

PowerQuery Ordner auswählen
PowerQuery Ordner auswählen

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.

PowerQuery Excel Dateien Importieren in Datenmodell
PowerQuery Excel-Dateien vorbereiten für den Import in das Datenmodell

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

Spalte Einfügen in PowerQuery
Benutzerdefinierte Spalte Einfügen in PowerQuery

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.

Each Excel Workbook PowerQuery Befehl
Benutzerdefinierte Spalte um alle Arbeitstabellen einzufügen

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.

Inhalt PowerQuery einstellen
Inhalt einer Tabelle in PowerQuery einstellen

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.

Spalten aus Excel Inhalt und Tabelle einfügen
Kleines Symbol um Inhalte aus Excel-Datei zu entnehmen

Es werden in Power Query automatisch neue Felder hinzugefügt.

PowerQuery mehrere Sheets Importieren mit Inhalt
PowerQuery mehrere Sheets Importieren mit Inhalt

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.

HC und FI-Controlling in PowerQuery mit allen Daten für Rechnungswesen und Personal-Controlling
Beispiel: HC und FI-Controlling in PowerQuery mit allen Daten

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.

PowerQuery Spalten und Inhalte herausfiltern
PowerQuery Spalten und Inhalte herausfiltern wie in Excel

Und drücken Sie auf dem Menü-Band „Home“ auf den Button „Schließen & laden“.

Daten von PowerQuery nach Excel laden
Daten von PowerQuery nach Excel 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

HC und Finanz Reporting in Excel aus PowerQuery
HC, FTE und Finanz Reporting in Excel aus PowerQuery

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.).

PowerQuery Spalten Formatieren um ELT Prozess zu beschleunigen
PowerQuery Spalten formatieren um ELT Prozess zu beschleunigen

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.

erweiterter Editor PowerQuery
erweiterter Editor PowerQuery

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

|

 
Kontakt