Dynamischer Dateipfad in PowerQuery

Power Query-Dateipfade sind dafür bekannt, dass sie statisch sind und dies ist einer der Gründe, warum die Wartung ziemlich umständlich ist. Stellen Sie sich vor, Sie haben gerade eine vollständig funktionierende Power Query-Verbindung eingerichtet und dafür eine Weile gebraucht. Und mit nur einer einfachen Änderung des Dateipfads funktioniert die Verbindung nicht mehr.

Machen Sie sich keine Sorgen mehr, denn wir zeigen Ihnen, wie Sie ganz einfach den Pfad der Datenquelle dynamisch hinterlegen können. Mit dem vorgeschlagenen Lösungsansatz können Sie den Ordner mit den PowerQuery-Dateien dorthin verschieben, wo Sie möchten, ohne, dass Sie etwas am Pfad anpassen müssen. Dies ist insbesondere dann sinnvoll, wenn Sie die Dateien an jemanden bspw. per Email versenden müssen.

Dynamischer Ordnerpfad in Power Query

Bevor wir beginnen, möchten wir nochmals darauf hinweisen, dass diese Methode zum Sammeln von Daten aus Dateien oder Ordnern Ihres PCs äusserst effektiv ist. Dies ist von Vorteil, wenn Sie Ihre Abfrage (Excel-Datei) und die Datenquelle bspw auf einem Netzwerk mit anderen teilen, der Dateipfad wird auch automatisch auf deren Ende aktualisiert.

Nehmen wir folgendes Beispiel: Ich habe diesen Ordner (Siehe Bild) auf meinem Computer mit 3 Excel-Dateien, die zu einer einzigen Datei zusammengeführt werden sollen.

Ordner auswählen

Ziel ist es, den Ordnerpfad dynamisch zu gestalten, sodass selbst wenn wir den Speicherort des Ordners ändern, die Abfrage automatisch aktualisiert wird und die Verbindung nicht unterbrochen wird.

Ordner erstellen

Schritt 1 – Ich erstelle eine neue leere Excel-Datei im selben Ordner.

Schritt 1 – Ich erstelle eine neue leere Excel-Datei im selben Ordner.

Hinweis – Es ist wichtig, die Sammeldatei (zusammengeführte Daten) am selben Ort wie die anderen Dateien zu speichern.

Formel einfügen

Schritt 2 – Ich habe die folgende Formel in die Sammeldatei eingefügt, um den aktuellen Pfad zu identifizieren. Diese Formel wird automatisch aktualisiert, auch wenn der Speicherort des Ordners geändert wurde.

=LEFT(CELL("filename"); SEARCH("\\["; CELL("filename")))

Schritt 2 – Ich habe die folgende Formel in die Sammeldatei eingefügt, um den aktuellen Pfad zu identifizieren.

Wie gezeigt, gibt die Formel den aktuellen Ordnerpfad zurück.

Wie gezeigt, gibt die Formel den aktuellen Ordnerpfad zurück.

Tabelle erstellen

Schritt 3 – Ein paar Schritte, bevor wir mit Power Query beginnen.

  • Erstellen Sie eine Tabelle aus dieser Zelle, in die wir die Formel eingefügt haben, drücken Sie Strg + T auf der Tastatur für die Verknüpfung.
  • Legen Sie den Header als „path“ und den Tabellennamen „dynamic_path“ fest.

Tabelle sollte in etwa so aussehen…

Tabellenbenennung

 

Power Query starten

Schritt 4 – Von hier aus müssen wir diese Tabelle „dynamic_path“ in Power Query importieren. Siehe Registerkarte „Daten“ und dann unter „Daten abrufen und transformieren“ auf „Aus Tabellenbereich“.

  • Klicken Sie mit der rechten Maustaste auf die erste Zeile und wählen Sie Drilldown aus
  • Dadurch wird der Pfad zu einem Text (Variable)

Die Abfrage sollte in etwa so aussehen …

Die Abfrage sollte in etwa so aussehen …

 

Erweiterter Editor in Power Query

Schritt 5 – Lassen Sie uns von hier aus tiefer eintauchen. Siehe Registerkarte „Ansicht“, dann unter „Erweitert“ „Erweiterter Editor“ auswählen.

  • Beachten Sie nach dem Drilldown den zweiten Schritt, der als „Pfad“ bezeichnet wird.
  • Fügen Sie im erweiterten Editor die folgende M-Code-Zeile hinzu.
let
    Source = Excel.CurrentWorkbook(){[Name="dynamic_path"]}[Content],
    path = Source{0}[path],
    GetFolderFiles = Folder.Files(path)
in
    GetFolderFiles

Codes im erweiterten Editor sollten in etwa so aussehen. Hervorgehobene Zeilen sind diejenigen, die wir gerade hinzugefügt haben.

Ergebnis

Ergebnis – Nachdem Sie alle oben genannten Schritte ausgeführt haben, sollten wir eine Liste der vorhandenen Dateien in unserem Ordner sehen können. Vergessen Sie nur nicht, die aktuelle Datei (Zusammengeführte Datendatei) aus der Liste herauszufiltern, sonst haben wir am Ende Datenduplikate.

Nachdem wir unseren dynamischen Pfad nun erfolgreich eingerichtet haben, würde eine Änderung des Pfads für den Ordner „Records“ dazu führen, dass unsere Abfrage weiterhin wie gewohnt funktioniert.

Den Dateipfad in Excel Power Query dynamisch gestalten

Falls Sie anstelle eines Ordnerpfads einen dynamischen Dateipfad erstellen müssen, können wir dieselbe Abfrage anpassen, um dies zu erreichen.

Wenden Sie im erweiterten Editor die folgenden Codeänderungen an.

let
    Source = Excel.CurrentWorkbook(){[Name="dynamic_path"]}[Content],
    path = Source{0}[path],
    GetFiles = Excel.Workbook(File.Contents(path & "Country - Germany.xlsx"))
in
    GetFiles

Falls Sie anstelle eines Ordnerpfads einen dynamischen Dateipfad erstellen müssen, können wir dieselbe Abfrage anpassen, um dies zu erreichen. Wenden Sie im erweiterten Editor die folgenden Codeänderungen an.

Erfahren Sie mehr über Power Query, indem Sie andere Artikel zu Power Query auf dieser Website durchsuchen. Insbesondere wie Sie Fehler in Power Query behandeln können oder lesen Sie eine Einführung in die M-Sprache von Power Query.

19.10.2023

Als ich mit PowerQuery gestartet habe, war ich vor allem von der Performance und der Einfachheit begeistert. Das Problem war jedoch, wie übermittle ich das erstellte Query unseren Kunden, so dass diese es ohne aufwändige Anpassungen sogleich verwenden können.

Mit dem in diesem Artikel vorgestellten Ansatz lösten wir ein wesentliches Problem. Wir müssen dem Kunden nur noch erklären, wie und wo die Excel-Datei gespeichert werden muss und sogleich funktioniert es.

https://www.exact-construct.ch

Benötigen Sie Excel-Hilfe?

Wenn Ihnen die Zeit fehlt, um sich selber mit Power Query zu befassen, dann rufen Sie uns an, wir erledigen dies sehr gerne für Sie. Unsere Kontaktdaten finden Sie auf unserer Webseite.

10 Kommentare zu „Dateipfad in Power Query dynamisch“

  1. kann ich das in eine vorhandene Abfrage einfügen? Einlesen des Pfads funktioniert aber irgendwie komme ich nicht weiter.

    1. Ja, dies geht auch. Wichtig ist einfach zu beachten, dass die Quelldatei sowie die Datei wo das PowerQuery erstellt wurde im selben Ordner liegt. Denn mit diesem vorgeschlagenen Ansatz lesen wir den Dateipfad der PowerQuery-Datei aus und gehen davon aus, dass die Quell-Datei(en) im selben Ordner liegen. Wenn die Quelldateien irgendwo anders liegen, dann müsste ein anderer Ansatz gewählt werdern.

  2. *begeistert* hab es prima in meine bestehenden Abfragen einbauen können…
    Allerdings bekomme ich es nicht hin, wenn die Dateien/ Ordner auf einem SharePoint liegen… :-(

    Fehlermeldung: Der angegebene Ordnerpfad muss ein gültiger absoluter Pfad sein….

    Gibt es hier auch eine Lösung?

  3. Hallo
    Obwohl ich alles nach der detaillierten Anleitung mache, erhalte ich stets die Fehlermeldung:
    Es wurde keine Excel-Tabelle mit der Bezeichnung „dynamic_path“ gefunden.
    Woran kann das liegen?

  4. Wenn ich in Excel das eingebe
    =LEFT(CELL(„filename“); SEARCH(„\[„; CELL(„filename“)))
    kommt bei mir in der Excel Zelle
    #Name?

    Woran kann das liegen ?

    1. Du hast vermutlich eine Deutsche Excel-Version. Versuch es mal mit dieser Formel: =LINKS(ZELLE(„dateiname“);FINDEN(„\[„;ZELLE(„dateiname“)))

Kommentar verfassen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Nach oben scrollen