Dateien mit ändernden Namen aus Ordnern mit ändernden Namen in Power Query laden
This post is also available in:
Englisch
Was erstmal kompliziert und wenig sinnvoll klingt, ist in der Praxis immer mal wieder anzutreffen. Vor kurzem stellte mich der automatisierte Export von Quelldaten eines anderen Programms im CSV-Format vor diese Aufgabe.
Täglicher Ordner für automatisierten Export
Leider ließ sich beim Export aus dem Quellsystem – abgesehen vom Zielverzeichnis – herzlich wenig einstellen. Die Folge ist, dass der automatisierte Export-Job täglich einen neuen Ordner mit dem aktuellem Datum erstellt, analog zu dem folgenden Beispiel:
In diesem Beispiel müssen lediglich die Dateien aus dem neusten Ordner geladen werden, da dieser jeweils die vollständigen Daten enthält. Allerdings ändern sich auch die Dateinamen mit dem täglichen Export. Die Struktur lässt sich aus der folgenden Darstellung erkennen:
Zum Laden der neusten Daten müssen wir somit:
- Den neusten Ordner ermitteln
- Das Datum aus dem Ordnernamen als Variable bzw. als Funktion speichern
- Den Ordner und die Dateien dynamisch mit der Variablen bzw. Funktion laden
1. Den neusten Ordner ermitteln
Um die Informationen über die vorhandenen Ordner herauszufinden, müssen wir diese erstmal ins Power Query laden. Wichtig hierbei, wir legen den Ordnernamen im Power Query gleich schon als Parameter an, da wir diesen zwingend für ein späteres dynamisches Laden benötigen:
Danach können wir den Ordner ins Power Query laden:
Hier erhalten wir viele Informationen über die Dateien, welche sich in den Unterordnern befinden. Allerdings nicht den Inhalt selbst. Dies wird oft missverstanden beim Laden von Ordnern. Uns interessiert aber nur die letzte Spalte “Folder Path”, welche uns den Pfad der jeweiligen Datei anzeigt:
Entsprechend entfernen wir alle anderen Spalten außer “Folder Path”, entfernen doppelte Werte, entfernen alles vor dem Trennzeichen “_” und sortieren absteigend, um den neusten Ordner an oberster Stelle zu erhalten:
Mit der keep top rows Funktion behalten wir nur noch den obersten/neusten Datensatz:
Jetzt kommt einer der entscheidenden Schritte. Aktuell haben wir lediglich eine Tabelle mit einem Datensatz. Damit wir den Ordnernamen und somit auch das Datum aus den Dateinamen später als dynamischen Wert haben, müssen wir auf diesen Wert mit der “drill down” Funktion heruntergehen. Dies lässt sich mit der rechten Maustaste erreichen:
Jetzt gibt es noch eine Sache anzupassen. Da wir manuell auf den Wert runter drillen, wird der Wert in der Formel statisch hinterlegt:
Dies würde bei einer Änderung des Ordnernamens einen Fehler hervorrufen. Daher ersetzen wir den statischen Wert, auf welchen herunter gedrillt werden soll, durch die Zeilennummer. Diese startet nicht bei 1, sondern wie oft in Programmiersprachen bei 0:
Da ich auf dieses Ergebnis im nächsten Schritt verweise, benenne ich die Query als “TemplateDynamicSourceFolder”:
2. Das Datum aus dem Ordnernamen als Parameter bzw. als Funktion speichern
In Schritt 1 haben wir somit den dynamischen Teil der Ordner- und Dateinamen extrahiert und theoretisch könnten wir in den anderen Abfragen auf dieses Ergebnis zugreifen.
Wenn wir eine der Sales_per_Employee Dateien geladen haben, können wir versuchen diese mit der gerade erstellten Funktion zu dynamisieren.
Dies können wir im erweiterten Editor direkt selbst ausprobieren:
Rein theoretisch sollte es funktionieren, allerdings kommt die folgende Fehlermeldung:
Formula.Firewall: Query 'Sales_per_Employee' (step 'Changed Type') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Grund des Fehlers ist – in der kurzen Version – dass Power Query es nicht erlaubt eine zusätzliche externe Datenquelle in der Query zu verwenden. Ken Puls hat dies in seinem Blog bereits detailliert beschrieben. Wir haben daher 2 Möglichkeiten, wie wir dies umgehen können:
- Wir können in die selbe Abfrage im Power Query unserer Abfrage aus dem ersten Schritt integrieren (s. Demo-Datei am Ende des Artikels)
- Wir können unsere in Schritt 1 erschaffene Abfrage in eine Funktion umwandeln
Womit wir auch zum letzten Schritt kommen
3. Den Ordner und die Dateien dynamisch mit der Variablen bzw. Funktion laden
Wie gesagt bekommen wir diesen Fehler nicht, wenn wir die Ermittlung des Ordnernamens aus Schritt 1 als Funktion definieren. Hierzu einfach mit der rechten Maustaste auf die Query aus Schritt1 klicken und “Create Function” auswählen:
Die Funktion benötigt noch einen Namen und einen Parameter. Da wir den Parameter aber bereits im ersten Schritt gesetzt hatten, müssen wir einfach einen Funktionsnamen vergeben:
Es wird automatisch ein neuer Ordner erstellt mit der Funktion, sowie der zugehörigen Query:
Beim Dynamisieren können wir nun die Jahreszahlen durch unsere Funktion ersetzen, welche ja das Datum des letzten Ordners zurückliefert:
Der Load funktioniert durch die Funktion fehlerfrei und aktualisiert sich immer auf die neusten Dateien aus dem neusten Ordner.
Download Beispieldatei und Quelldateien für diesen Artikel
