Dass sich mit Power Query alle Dateien aus einem Ordner zusammenfassen lassen, ist vielen bekannt. Was aber, wenn es mehrere Ordner sind? Und was, wenn nicht alle, sondern nur ausgewählte Dateien aus diesen Ordnern zusammengefasst werden sollen?
Wie das in drei Schritten geht, zeige ich, indem ich das Beispiel aus meinem Blogbeitrag vom 3. Juli 2023 erweitere.
Bild 1: Aus den oben gezeigten drei Ordnern nur die markierten Dateien zusammenführen
1) Liste mit den betreffenden Ordnern und Dateien anlegen und in Power Query einlesen
Zuerst hinterlege ich in einer »intelligenten« Tabelle die Pfade und Namen der Dateien, die zusammengeführt werden sollen.
Das erledige ich wie folgt:
- Ich erstelle zwei Spaltenüberschriften Ordner und Dateiname und belasse die Markierung in einer der zwei Zellen.
- Mit Strg+T und anschließendem Setzen des Häkchens bei Tabelle hat Überschriften erzeuge ich eine leere »intelligente« Tabelle.
- Über die Registerkarte Tabellenentwurf gebe ich ihr den Namen tbl_Lieferdateien.
- Nun trage ich wie in Bild 2 gezeigt in die Tabelle die gewünschten Ordnerpfade und Dateinamen ein.
Bild 2: In einer »intelligenten« Tabelle alle einzulesenden Dateipfade hinterlegen
Diese Tabelle lese ich nun in Power Query wie folgt ein.
- Ich klicke in die Tabelle und wähle auf der Registerkarte Daten > Aus Tabelle/Bereich.
- Die Abfrage benenne ich um in Lieferungen (Bild 3 rechts).
Bild 3: Die Liste der zusammenzuführenden Dateien in Power Query einlesen
2) Prototyp anlegen: Eine einzelne Datei einlesen und die dabei erzeugte Abfrage als Funktion bereitstellen
Für Dateien, die in Power Query eingelesen werden, sind meist einige Bereinigungsschritte nötig, bevor sie weiterverarbeitet werden können. Das ist auch hier so. Wie im Blogbeitrag vom 3. Juli 2023 gezeigt, muss jede einzelne Lieferungsdatei zunächst um Spalten mit den Kopfdaten erweitert werden.
Damit ich das später automatisch für alle Dateien erledigen lassen kann, baue ich einen Prototyp. Dieser erhält die gleichen Transformationsschritte, wie die Musterdatei im oben genannten Blogbeitrag.
- Im Power Query-Editor wähle ich über Start > Neue Quelle > Datei > Text/CSV eine der betreffenden Dateien aus. Die Abfrage benenne ich LieferungAuslesen.
- Auf der Registerkarte Start klicke ich auf Zeilen entfernen > Erste Zeilen entfernen > 5.
- Anschließend verschiebe ich mit Start > Erste Zeile als Überschriften verwenden die Überschriften nach oben.
- Ich wähle Spalte hinzufügen > Benutzerdefinierte Spalte > Neuer Spaltenname: Lieferant. Als benutzerdefinierte Spaltenformel gebe ich ein: =Quelle[Column2]{0}.
- Den letzten Schritt wiederhole ich noch zweimal,
– einmal für die Warengruppe mit der Formel =Quelle[Column2]{1} und
– einmal für das Lieferdatum mit der Formel =Quelle[Column2]{2}.
Bild 4: Eine Datei als Prototyp einlesen und transformieren
Den Code verwende ich nun, um daraus eine Funktion zu erstellen:
- Zuerst lasse ich mir den in Bild 5 gezeigten kompletten Code der Abfrage LieferungAuslesen im Erweiterten Editor (Start > Erweiterter Editor) anzeigen.
- Die variablen Werte – wie hier Pfad und Dateiname – schreibe ich wie folgt als Parameter vor den M-Code: (Ordner, Dateiname ) => (Bild 6 oben).
- Im M-Code tausche ich dann den festen Wert für den Dateipfad gegen eine Kombination der beiden Parameter aus. Ich verkette diese mit einem Backslash (\) und dem &-Zeichen zu einem kompletten Dateipfad wie in Bild 6 gezeigt.
Bild 5: Der Code des Prototypen mit fester Angabe zu Dateipfad und -name
Bild 6: Der Code des Prototypen mit dynamischer Pfadangabe umgewandelt in eine Funktion
3) Alle Dateien der Liste entpacken mit Hilfe der eben erstellten benutzerdefinierten Funktion
Nun muss ich nur noch die ursprüngliche Abfrage Lieferungen mit Hilfe der selbst erstellten Funktion LieferungAuslesen erweitern.
Das erledige ich wie folgt:
- Über Start > Benutzerdefinierte Funktion aufrufen gebe ich einen Spaltennamen ein, wähle als Funktionsabfrage: LieferungAuslesen, als Ordner den Spaltennamen Ordner und als Dateiname den Spaltennamen Dateiname.
- Ein Klick auf den Doppelpfeil der neuen Spalte Lieferinhalt entpackt alle genannten Dateien nach demselben Muster und fügt sie auch gleich untereinander an.
Bild 7: Mit der neuen Funktion eine benutzerdefinierte Spalte erstellen
- Zum Schluss passe ich noch die Datentypen an und lade die Liste nach Excel.
Bild 8: Per Klick auf den Doppelpfeil alle Dateien nach dem gleichen Muster entpacken
Fazit
Mit Hilfe einer Funktion kann ich eine flexible Möglichkeit schaffen, um alle in einer Liste genannten Dateien aus mehreren Ordnern automatisch in gleicher Art aufbereiten und untereinander anfügen zu lassen.
Eine bewährte Vorgehensweise dafür ist:
- Erstellen eines Single-Use-Szenarios.
- Konvertieren des Single-Use-Szenarios in eine Funktion.
- Nutzen dieser Funktion in einer anderen Abfrage.
TIPP: Wer hier im Blog ALLE Rezepte zu Power Query finden möchte, gibt einfach oben rechts in das Suchfeld Power-Query-Rezept ein.
Noch tiefer eintauchen in Power Query?
Am 25. Oktober 2023 gibt es die nächste Gelegenheit dafür in meinem Online-Kurs Power Query für Fortgeschrittene: Mit eigenen Abfrage-Tools die Datenaufbereitung optimieren
Flyer als PDF herunterladen
