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.
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.
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")))
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…
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 …
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.
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.
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.
Super, das löst mir gerade ein Problem, danke!
kann ich das in eine vorhandene Abfrage einfügen? Einlesen des Pfads funktioniert aber irgendwie komme ich nicht weiter.
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.
*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?
Hallo Marco
Der einfachste Weg aus meiner Sicht ist es, wenn du den Pfad zu einem Laufwerk zuordnest (Unter: Dieser PC -> Netzwerklaufwerk verbinden). Wenn dies keine Option ist, dann gibt es hier einen Ansatz, wie es gelöst werden kann. Ist aber nicht ganz trivial: https://www.thebiccountant.com/2020/11/16/improve-file-import-from-sharepoint-in-power-bi-and-power-query/
Viele Grüsse
Adrian
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?
Wir haben hier noch ein zweiter Artikel zum gleichen Thema. Vielleicht hilft dieser weiter: https://www.excel-hilfe.ch/blog/dynamischen-dateipfad-in-power-query-erstellen/
Wenn ich in Excel das eingebe
=LEFT(CELL(„filename“); SEARCH(„\[„; CELL(„filename“)))
kommt bei mir in der Excel Zelle
#Name?
Woran kann das liegen ?
Du hast vermutlich eine Deutsche Excel-Version. Versuch es mal mit dieser Formel: =LINKS(ZELLE(„dateiname“);FINDEN(„\[„;ZELLE(„dateiname“)))
Ihr müsst auf die Anführungszeichen achten, diese ggf. ersetzen, sonst kann es sein, dass excel die nicht erkennt