Dynamische Dateipfadverwaltung in Excel und Power BI

Haken: Fest codierte Pfade sind stille Projektkiller. Verschieben Sie eine Datei, geben Sie eine Arbeitsmappe frei oder veröffentlichen Sie ein PBIX – und bumm: Die Aktualisierung schlägt fehl.

Ziel: In ca. 10 Minuten richten Sie robuste, parametergesteuerte Pfade für Excel (Power Query) und Power BI (Desktop & Service) sowie ein kleines, wiederverwendbares M-Muster ein, das Sie in jedes Projekt einfügen können.

1) Warum fest codierte Pfade fehlschlagen

Wenn Sie „C:\Benutzer\Alice\Projekt\data.csv“ in eine Abfrage einbetten, erhalten alle Benutzer, deren Dateien woanders gespeichert sind (z. B. D:\Arbeit\Daten\), Fehlermeldungen. Auch das Verschieben von Dev → Test → Prod oder der Power BI-Dienst benötigt ein Gateway, um auf lokale Dateien zuzugreifen, führt zu Fehlern. Die Lösung: Zentralisieren Sie den Pfad an einer Stelle (einen Parameter oder einen detaillierten Zellenwert) und verweisen Sie überall darauf.

2) Die universelle Lösung: Parameter

Idee: Erstellen Sie einen einzelnen Textparameter (z. B. FolderPath) und verketten Sie den Dateinamen im Quellschritt. Ändern Sie den Parameter einmal – jede Abfrage folgt.

Power BI Desktop – Schnelle Schritte

1. Home ▸ Home ▸ Parameter verwalten ▸ Neuer Parameter

   • Name: Ordnerpfad (Typ: Text )

   • Aktueller Wert: zB C:\Data\Source\

2. Ersetzen Sie wörtliche Pfade in Abfragen durch den Parameter:

let
    Source = Excel.Workbook(
        File.Contents(FolderPath & "Data.xlsx"),
        null, true
    )
in
    Source

Servicebonus: Öffnen Sie nach der Veröffentlichung den Datensatz ▸ Einstellungen ▸ Parameter und richten Sie den Ordnerpfad auf Prod (plus Gateway, falls vor Ort). Keine erneute Veröffentlichung erforderlich.

3) Excel-Optionen (Parameter oder relativer Pfad)

Option A – Excel-Parameter (dasselbe wie PBI)

Erstellen Sie ExcelPath über Daten ▸ Daten abrufen ▸ Power Query-Editor starten ▸ Parameter verwalten und verwenden Sie es dann genau wie FolderPath.

Option B – Relativ zur Arbeitsmappe (keine Benutzereingabe)

Geben Sie dies in eine beliebige Zelle ein, um den Ordnerpfad der Arbeitsmappe zu erhalten:

=LINKS(ZELLE("Dateiname",$A$1),FINDEN("[",ZELLE("Dateiname",$A$1))-1)

Wandeln Sie diese Zelle in eine einzeilige Tabelle um (z. B. DynamicPath[Path] ), holen Sie Daten aus Tabelle/Bereich , führen Sie dann einen Drilldown zum Text durch und geben Sie ihn in Ihren Quellschritt ein.

4) Einfügebereites M-Muster (verarbeitet Schrägstriche und Blattauswahl)

Verwenden Sie dies einmal pro Abfrage, um Fehler durch abschließende Schrägstriche zu vermeiden. Es unterstützt \ und / und ist daher sicher für Windows-Pfade und URL-Pfade (z. B. SharePoint-Konnektoren).

let
    // 1) Normalise a folder path to end with the correct slash
    EnsureSlash = (t as text) as text =>
        if Text.EndsWith(t, "\\") or Text.EndsWith(t, "/") then t
        else if Text.Contains(t, "/") then t & "/"
        else t & "\\",

    // 2) Use a parameter (rename fPath to your parameter name)
    FolderPath = EnsureSlash(fPath),
    FilePath   = FolderPath & "Area_Table.xlsx",

    // 3) Load the workbook and pick the wanted item
    Source   = Excel.Workbook(File.Contents(FilePath), null, true),
    AreaData = Source{[Item="AreaData", Kind="Sheet"]}[Data]
in
    AreaData

CSV-Variante (gleicher Parameter):

let
    EnsureSlash = (t as text) as text =>
        if Text.EndsWith(t, "\\") or Text.EndsWith(t, "/") then t
        else if Text.Contains(t, "/") then t & "/"
        else t & "\\",

    FolderPath = EnsureSlash(fPath),
    FilePath   = FolderPath & "AreaData.csv",
    Source     = Csv.Document(
                    File.Contents(FilePath),
                    [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]
                 ),
    Promoted   = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    Promoted

Ordnerimport (alle Dateien in einem übergeordneten Ordner):

let
    EnsureSlash = (t as text) as text =>
        if Text.EndsWith(t, "\\") or Text.EndsWith(t, "/") then t
        else if Text.Contains(t, "/") then t & "/"
        else t & "\\",

    BaseFolder = EnsureSlash(BasePath),   // Text parameter
    Files      = Folder.Files(BaseFolder),
    // e.g., only CSVs:
    Filtered   = Table.SelectRows(Files, each Text.EndsWith([Extension], ".csv"))
in
    Filtered

5) SharePoint / OneDrive-Notiz

Bevorzugen Sie Cloud-Konnektoren, damit der Dienst ohne Gateway aktualisiert werden kann. Parametrisieren Sie die Site-/Stamm-URL und kombinieren Sie sie mit einem Dateinamen.

let
    // SharePoint site/root URL as a Text parameter, e.g. "https://tenant.sharepoint.com/sites/Finance"
    SiteUrl   = SharePointSiteUrl,
    Files     = SharePoint.Files(SiteUrl, [ApiVersion=15]),
    PickFile  = Table.SelectRows(Files, each [Name] = "Area_Table.xlsx"),
    Binary    = PickFile{0}[Content],
    Imported  = Excel.Workbook(Binary, null, true)
in
    Imported

Wenn Sie innerhalb der Site nach Ordnerpfad speichern, können Sie auch nach [Ordnerpfad] oder [Relativer Pfad] filtern.

6) Best Practices, die Stunden sparen

• Einzelner übergeordneter Ordner. Ein Parameter (BasePath) steuert alles (BasePath & „Sales\FY2025.csv“ oder Folder.Files(BasePath)).

• Name und Dokumentparameter. BasePath, SharePointSiteUrl, Umgebung usw. Fügen Sie eine kleine „Read Me“-Seite hinzu, auf der erklärt wird, wie diese geändert werden.

• Halten Sie sich strikt an die Struktur. Parameter ändern sich wo , nicht was . Stellen Sie sicher, dass Dateinamen und Schemata in allen Benutzern und Umgebungen übereinstimmen.

• Realitätscheck zur Serviceaktualisierung.

  • Lokale Pfade → benötigen On-Premises Data Gateway .

  • SharePoint/OneDrive-URLs → kein Gateway; parametrisieren Sie die URL und alles ist in Ordnung.

7) Schritt für Schritt: So funktioniert es in Ihrer Datei

1. Erstellen Sie den Parameter (fPath / FolderPath) als Text .

2. Normalisieren Sie mit EnsureSlash und erstellen Sie FilePath.

3. Laden Sie das genaue Blatt/die genaue Tabelle ([Item="AreaData", Kind="Sheet"]) und navigieren Sie dorthin.

4. Tauschen Sie Umgebungen aus, indem Sie einen Parameterwert ändern.

5. Datensatz veröffentlichen (PBI) und bearbeiten ▸ Parameter für Prod.

6. Optional nur Excel: Verwenden Sie den Zellentrick mit dem relativen Pfad anstelle eines Parameters.

8) Fehlerbehebung (schnelle Erfolge)

• Zugriff im Dienst verweigert: falscher Connector (lokale Synchronisierung vs. Cloud) oder fehlendes Gateway.

• „Element nicht gefunden“ in Excel.Arbeitsmappe: Blatt-/Tabellenname geändert – öffnen Sie die Quelle und kopieren Sie den genauen Elementnamen.

• Gemischte Schrägstriche: Führen Sie Pfade immer durch EnsureSlash.

• Datenschutzhinweise: Passen Sie die Datenschutzebenen an oder vermeiden Sie unnötige domänenübergreifende Zusammenführungen.

9) TL;DR

Zentralisieren Sie Pfade mit einem Textparameter (oder einem relativen Excel-Pfad), verketten Sie den Dateinamen in M und veröffentlichen Sie sicher. Sie müssen keine Pfade mehr festlegen und können mit der Auslieferung beginnen.

Abschluss

Dynamische Dateipfade beseitigen den Ärger über defekte Links in gemeinsam genutzten Excel- und Power BI-Projekten. Durch die Nutzung von Power Query-Parametern (oder die geschickte Verwendung von Arbeitsmappenfunktionen in Excel) können Sie Ihre Datenquellenpfade flexibel und einfach anpassen. Diese Lösung funktioniert in allen Szenarien – auf Power BI Desktop, im Power BI-Dienst oder in Excel –, da sie den maschinenspezifischen Teil des Pfads in eine konfigurierbare Einstellung abstrahiert. Mit einer einmaligen Einrichtung sind Ihre Abfragen für jeden Benutzer und jede Umgebung portierbar. Jeder Benutzer kann den Bericht auf seinem eigenen Computer mit minimalen Anpassungen aktualisieren und sicher sein, dass die Aktualisierung eines einzigen Parameters (oder das Verschieben eines Ordners) ausreicht, um die Datenquelle neu zu verknüpfen. Kurz gesagt: Parameter sind die ideale Lösung für dynamische Pfade und bieten eine robuste, zu 99 % automatisierte Lösung für jedes Projekt und jeden Benutzer.

---

Benötigen Sie in jedem Power BI- oder Excel-Projekt ein Power Query-Starterpaket? Nur mit einer kleinen Anpassung für schnelle und effiziente Berechnungen. Probieren Sie es aus.

Power Query-Toolkit

€19.00

Jetzt kaufen