Filenamen beim Verzeichnisimport mit Power Query integrieren

Publiziert von

Power Query Folder Import TitleDie automatisierte Verarbeitung von gleichartigen Quelldatenfiles in eine einzige analysefähige Datentabelle ist eine sehr häufige Anforderung in der täglichen BI Praxis. Beispielsweise bei der Verarbeitung von Tagesfiles aus der Produktion, Firmenfiles in der Konzernkonsolidierung, Monatsfiles aus der Lohnverrechnung oder wöchentliche Logfiles von der Website.

Power Query bietet mit dem Verzeichnisimport ein sehr mächtiges Instrument für diese Aufgabe, durch den Zugriff auch auf die Metadaten der Quelldateien lassen sich sogar der Dateiname und der Dateipfad in den Datenbestand integrieren und damit fehlende Informationen im Datenfile kompensieren

Ausgangsituation

Wir erhalten in unserem Anwendungsfall annahmegemäß jeden Tag von unserer Produktionsabteilung eine CSV-Datei mit den täglichen Produktionsdaten, vereinfachend arbeiten wir hier nur mit 3 Tagesdateien:

Source_01

Jede dieser Dateien enthält die beiden Spalten Produktnummer und Produktionsmenge:

Source_02

Ziel ist es, alle diese Tagesdateien in einem einzigen analytischen Datenmodell mit Power Pivot auszuwerten. Zur Bündelung aller Quelldateien in einer (langen) Faktentabelle nutzen wir den Verzeichnisimport von Power Query.

Verzeichnisimport mit Power Query

Im Ribbon Power Query ist im Bereich Aus Datei der Eintrag Aus Ordner zu finden:

PQFolder_01

Einfach den Pfad zum Ordner mit den CSV-Dateien auswählen …

PQFolder_02

… und schon erhalten wir im ersten Schritt die Metadaten der Importdateien aufgelistet. Da die CSV-Dateien selbst nicht die Information enthalten, auf welchen Tag sich die Daten eigentlich beziehen, müssen wir den Dateinamen (und sicherheitshalber auch gleich den Dateipfad) in die Verarbeitung einbeziehen. Dazu reduzieren wir die angezeigten Spalten auf die folgenden drei (mit dem Befehl „Remove“ auf die nicht benötigten Spalten) …

PQFolder_03

… und fügen eine „Custom Column“ hinzu:

PQFolder_04

Die Formel lautet „Csv.Document([Content])“ (mehr zur Power Query Formelreferenz):

PQFolder_05

Danach wird auch die erste Spalte Content entfernt …

PQFolder_06

… und der Expand-Button der zuvor definierten Spalte „CustomContent“ betätigt und die Auswahl mit OK bestätigt:

PQFolder_07

Power Query hat jetzt die beiden Felder Name und Folder Path als gemeinsame Liste mit den Datenspalten der jeweiligen CSV-Dateien verarbeitet:

PQFolder_08

Sehr wichtig ist jetzt, nicht den an dieser Stelle üblichen Button Use first Row as Headers verwenden, da in diesem Fall der Dateiname und der Pfad der ersten Datei im Verzeichnis übernommen werden würde. Sobald diese Datei nicht mehr vorhanden ist oder nicht mehr die erste Datei im Verzeichnis ist, würde es zu einer Fehlermeldung beim Aktualisieren kommen (vielen Dank für diese scharfsinnige Analyse von einem unserer Trainingsteilnehmer!). Stattdessen einfach die 3. Spalte auf Product und die 4. Spalte auf Qty manuell umbenennen.

Jetzt kommt ein sehr wichtiger Schritt:
Power Query kann nur die Überschriftszeilen der ersten importierten CSV-Datei mit dem Befehl „Use first Row as Headers“ korrekt verarbeiten, die Überschriftszeilen der weiteren CSV-Dateien befinden sich als Datensätze noch in der Datenliste. Diese werden jetzt in einem – grundsätzlich beliebigen – Feld ausgefiltert, wir verwenden das Feld Product um den Zeilenkopf-Filter zu definieren:

PQFolder_09

Abschließend werden jetzt die Formate für die einzelnen Spalten festgelegt und der Name der Power Query Abfrage mit Folder Daily Production festgelegt:

PQFolder_10

Wichtig für den Abschluß ist, daß jetzt nicht einfach der Button Close & Load gedrückt wird sondern die Detailoption Close & Load To … aufgerufen wird:

PQFolder_11

Dadurch gelangen wir in den wichtigen Auswahldialog zur Steuerung, wohin die Daten jetzt geladen werden sollen. Für die Verwendung in Power Pivot ist die Führung der Daten über einen Excel Table kontraproduktiv, daher die Optionen Only Create Connection und Add this data to the Data Model (= Hinzufügen zum Power Pivot Datenmodell) aktivieren:

PQFolder_12

Die Daten werden jetzt (nach Power Pivot) geladen, in Excel ist in der Pane Workbook Queries zu sehen, wieviele Datensätze verarbeitet wurden und ob Fehler aufgetreten sind:

PQFolder_13

Auswerten mit Power Pivot

Die schwierige Aufgabe ist jetzt geschafft, die Auswertung mit Power Pivot ist vergleichsweise einfach. Mit der Calculated Column Production Date wird aus dem Dateinamen das Datum herausgeschält und durch eine Date-Formel sauber im Datumsformat produziert:

Power Pivot 01

Natürlich wäre jetzt für die sprechende Auswertung auch eine Zeit- und eine Product-Dimension anzulegen, da es aber in diesem Blogbeitrag darum nicht geht überspringen wir diese Schritte. Die Analyseanwendung ist jetzt also fertig, wir können alle eingelesenen CSV-Dateien jetzt in einem einzigen Datenmodell auswerten (und haben immer noch den Bezug auf den Quelldateinamen und auch den Quellpfad als wichtige Quellinformationen):

Power Pivot 02

Weiterführende Quellen:

exceleratorbi.com.au/power-query-combine-multiple-files-in-folder-another-method

blogs.adatis.co.uk/blogs/dan/archive/2014/09/25/importing-multiple-files-into-power-query

Power Pivot Formelreferenz: msdn.microsoft.com/library/Mt253322?ui=en-US&rs=en-US&ad=US

Sehr interessant: www.youtube.com/watch?v=yL11ugShdrk

Verzeichnisimport für Excel Files:

www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

www.powerpivotpro.com/2015/07/consolidated-worksheets-with-power-query/

Robert Lochner

Robert Lochner ist seit 2001 als Unternehmer tätig und Gründer der Linearis. Seit seinem Betriebswirtschaftsstudium an der WU Wien ist er als Unternehmer in der BI Branche tätig, ist Trainer für Excel BI und Power BI und unterstützt Unternehmen bei der digitalen Transformation ihrer Business Intelligence Prozesse. Er ist Autor des Linearis Blogs und mehrerer fachlicher Publikationen.

Weitere Beiträge

Kategorien: Analyse, Datawarehousing, Excel als BI Frontend, Power BI im Team nutzen, Power Pivot nutzen