Für den sogenannten Folderimport - also dem Import einer variablen Anzahl von gleichartigen XLSX-/CSV-/TXT-/JSON-/XML-Files aus einem Verzeichnis - steht in Power BI Desktop und in Excel die sehr mächtige "Combine Files" Funktion zur Verfügung. Diese Funktion ("Folderimport V2") ist seit dem November 2016 Release von Power BI Desktop verfügbar und stellt die Nachfolgelösung für die ursprünglich einfachere, aber auch deutlich weniger leistungsfähige "Combine Binaries" Funktion dar ("Folderimport V1").

Im Jahr 2015 haben wir im Blogpost Filenamen beim Verzeichnisimport mit Power Query integrieren bereits die V1 Funktionalität vorgestellt. Wir schließen hier thematisch an diesen Beitrag an und lösen den gleichen Anwendungsfall mit der V2 Funktionalität. Die Umsetzung wird hier mit Power Query in Power BI Desktop gezeigt, die Vorgehensweise kann aber fast 1:1 auch mit Power Query in Excel angewendet werden.

Vorteile der Combine Files Funktion

• Die Transformation der Quelldaten findet vor dem Zusammenfügen in die Zieltabelle statt (und nicht nachher, daher müssen jetzt nicht mehr Headertitel usw. nachträglich weggefiltert werden)
• Combine Files funktioniert mit allen unterstützten File Formaten (XLSX, CSV, TXT, JSON, XML) und nicht nur mit CSV und TXT.
• Der Combine Files Wizard erzeugt ein perfektes Lernbeispiel für "Schleifenlösungen" (= schleifenartige Ausführung der Transformationen aus einer Sample Query mittels einer Function). Mit diesem Vorbild können rasch noch weiter fortgeschrittene Aufgaben mittels Functions gelöst werden.

Ausgangssituation

Dieser Beitrag schließt an den Usecase in oben genanntem Blogbeitrag aus dem Jahr 2015 an. Annahmegemäß erhalten wir in diesem Anwendungsfall jeden Tag von unserer Produktionsabteilung eine CSV-Datei mit den täglichen Produktionsdaten, vereinfachend arbeiten wir hier nur mit 3 Tagesdateien:

Jede dieser Dateien enthält die beiden Spalten Produktnummer und Produktionsmenge, der Tag ergibt sich nur aus dem Dateinamen:

Ziel ist es, den Inhalt der 3 Dateien automatisiert und dynamisch in eine einzige Faktentabelle zu bündeln. Die Lösung muss also auch gleichermaßen mit 5 oder 50 Dateien funktionieren.

1. Erstellen eines Folderimports

Der Konnektor für den Folderimport wird in Power BI im Get Data Dialog in der Gruppe File angeboten:

Zuerst wird der Pfad mit den zu importierenden Files ausgewählt:

Als nächstes wird die (tabellarische) Vorschau des Inhalts des selektierten Folders gezeigt:

Der Button Load sollte nicht gedrückt werden, da damit lediglich diese Folder-Metadaten in eine Tabelle in Power BI geladen werden. Der Button Combine ist dann sinnvoll, wenn mit Sicherheit keine besonderen Transformationen an den eingelesenen Daten notwendig sind. Da dies idR zu diesem Zeitpunkt noch nicht beurteilt werden kann, sollte immer der Button Edit verwendet werden.

Dieser öffnet den Power Query Editor, hier könnte beispielsweise die Liste der Dateien gefiltert werden (etwa auf die Extension ".csv" falls auch andere, nicht importrelevante Files sich in dem Folder befinden). Ist das Binärfeld Content markiert, kann die Funktion Combine Files aufgerufen werden:

Der folgende Dialog ist formatspezifisch, d.h. der Dialog sieht beim Import von CSV-Files anders aus als beim Import von XLSX- oder JSON-Files. Wesentlich ist hier die Auswahl First File, da damit die sogenannte Sample Query nicht auf einer bestimmten Datei im Folder sondern auf der ersten Datei aufgebaut wird. Das hat den Vorteil, daß die Sample Query immer funktionieren wird, nämlich auch dann, wenn das heutige Bezugsfile eines Tages nicht mehr im Folder enthalten sein wird.

Im nächsten Schritt wird schon das fertige Ergebnis generiert: einerseits eine relativ kompliziert aussehende Objektstruktur in der Queries Pane (links) und andererseits die perfekte Outputtabelle Daily Production mit den Datensätzen aus allen 3 Quelldateien sowie dem Filenamen:

2. Verstehen der Sample Query (incl. Function)

Die oberen 4 Objekte in der Queries Pane (links) bilden die sogenannte Sample Query, die dann in einem zweiten Schritt als Function auf die Dateien der Folderliste angewendet wird.

Das erste Objekt (vom Typ "Binary") ist das Sample File, das über die relative Adressierung ("First File") im Combine Files Dialog bestimmt wurde und aktuell aus dem Inhalt des Folders geladen wird:

Das zweite Objekt ist ein Parameter, der das File als Ganzes (Typ "Binary") referenziert:

Das dritte Objekt (Typ "Query") ist die für uns sehr wichtige Sample Query: hier finden die Transformationen der Datenfiles - stellvertretend für alle Files anhand des ersten Files - statt. Diese Transformationen können hier "ganz normal" weiterentwickelt werden und werden dann auf sämtliche Files des Folders angewendet (eine einfache Demo dazu gibt es dann weiter unten):

Das vierte Objekt ist eine Function, die nichts anderes macht, als die Transformationsschritte der Sample Query als aufrufbare Function bereitzustellen:

Das Bemerkenswerte dabei ist, daß der M-Code der Function mit einer Art "automatischem Sync" mit der Sample Query verbunden ist. Power Query spiegelt also den M-Code der Sample Query in die Function. Dieser Sync bleibt solange aufrecht, solange der M-Code der Function nicht manuell bearbeitet wird. Dabei genügt ein an sich irrelvanter zusätzlicher Zeilenumbruch, um den Sync zu beenden. Daher beim Aufrufen des Advanced Editor am besten immer mit Cancel und nicht mit OK beenden. Power Query zeigt beim Öffnen des Advanced Editor ohnehin den entsprechenden Hinweis auf diese Funktionalität an:

Es stellt sich natürlich die Frage, wozu es gleich 4 Objekte braucht:

• Die Sample Query und die Function werden getrennt, weil auf diese Weise die Sample Query wie gewohnt bearbeitet und laufend weiterentwickelt werden kann. Wie oben im Screenshot zu sehen ist, wird im Power Query Editor bei einer Function nur 1 Step Source angezeigt, die enthaltenen Transformationen könnten also nur noch im Advanced Editor auf Code Ebene verändert werden.
• Damit eine Function mit "Sync" erzeugt werden kann, muß die zugrundeliegende Query parametrisiert sein (das macht auch Sinn, da nur so die Variabilität des Function Aufrufs an die Query übergeben werden kann). Daher wird auch ein Parameter Objekt benötigt.
  Übrigens: eine Function mit "Sync" kann für jede Query auch manuell erzeugt werden (daher ist der Folderimport ein sehr gutes Lernbeispiel für noch fortgeschrittenere schleifenartige Importlösungen):
  
• Der Parameter könnte auch schlicht den Pfad zum Folder aufnehmen, warum also das zusätzliche Binary Objekt und die Verlinkung über den Parameter in die Sample Query? Nun, da bin ich auch nicht sicher. Ich vermute, daß die relative Adressierung des ersten Files nur auf diese Weise möglich ist ...

3. Verstehen der Combined Query (inkl. Aufruf der Function)

Wie schon eingangs gesehen, werden die Metadaten des Importfolders in der Query Daily Production im ersten Step Source als Tabelle eingelesen:

Diese Tabelle wird auf eventuelle im Ordner befindliche "Hidden Files" gefiltert und dann findet der entscheidende Schritt statt: der zeilenweise Aufruf der Function aus der Sample Query mit Übergabe des jeweiligen File Pfades als Parameter. Die Sample Query wird also für jedes File im Importfolder abgearbeitet ("Schleife"), vorerst wird die erzeugte Tabelle (in gewohnter Power Query Manier) erst mal nur verdichtet (als Table-Link) angezeigt:

Im nächsten Schritt wird ein Expand auf die Spalte mit dem Funktionsaufruf durchgeführt und auf diese Weise die Felder und Datensätze der dahinterliegenden Tabellen in die Metadatenliste geholt und damit die fertige Output Tabelle erzeugt:

Eine kleine Schwachstelle gibt es:

• Die Formatkonvertierungen der Sample Query kommen leider nicht in der Combined Query an und müssen daher mit einem abschließenden Step Changed Type hier wiederholt werden. Weitere nicht übertragene Steps sind uns nicht bekannt.

4. Einfache Anpassungen an der Default Lösung

Diese vom Combine Files Wizard erzeugte Lösung ist jetzt keineswegs starr sondern kann leicht auf die eigenen Anforderungen angepaßt / weiterentwickelt werden.

4.1 Anpassungen an der Sample Query

Der Sample Query können beliebige Transformationen hinzugefügt werden, diese werden allesamt an die Function und damit an die Combined Query angewendet. Hier haben wir beispielhaft eine Custom Column hinzugefügt und eine Datentyp-Konvertierung vorgenommen (die ja leider als einziges nicht in der Combined Query ankommen wird):

Die erweiterten Transformationsregeln werden dank des aktiven "Syncs" in die verknüpfte Function gespiegelt (diese braucht also nicht manuell angepaßt zu werden nach einer Änderung):

Das Ergebnis ist auch sofort in der Combined Query sichtbar, lediglich die Datentyp-Konvertierung muß (manuell) wiederholt werden (und war insofern überflüssig in der Sample Query).

4.2 Anpassungen an der Combined Query

Aus der Metadatenliste des Importfolders können bei Bedarf ganz einfach weitere Attribute ...

... in den Output Table übernommen werden:

4.3 Anpassungen an der Objektstruktur

Die automatisch erzeugte Ordner- und Objektstruktur kann beliebig nach eigenen Vorstellungen verändert und übersichtlicher gestaltet werden (die Umbenennungen werden automatisch an die abhängigen Objekte durchgereicht):

Fazit

Der Combine Files Wizard ist sehr leistungsfähig und benutzerfreundlich zu bedienen, wenn die Grundzusammenhänge der Objektstruktur erst einmal klar ist. Mit diesem Know-How ist es dann auch nur noch ein kleiner Schritt, eigene Schleifenlösungen für noch fortgeschrittenere Szenarien zu erstellen.

Der einzige Schwachpunkt ist die fehlende Übertragung der Datentyp-Konvertierung aus der Sample Query in die Combined Query, im Normalfall sind hier aber nur einige Klicks zu wiederholen.

Quellen:

docs.microsoft.com/en-us/power-bi/desktop-combine-binaries

Video Tutorial: Combine Files in Power BI Desktop and Power Query - The New Easy way

Video Tutorial: Power BI and Power Query Parameters and Functions

blog.crossjoin.co.uk/2016/05/15/creating-m-functions-from-parameterised-queries-in-power-bi/