Excel PowerQuery dynamisch - itService Thomas Käflein

Stand 27.02.2021

Werden externe Daten aus Dateien in PowerQuery geladen, so wird der zugrundeliegende Dateipfad physikalisch als Quelle gespeichert. Relative Dateipfade sind nicht vorgesehen. Das macht Umstrukturierungen bei den Daten sehr umständlich und arbeitsaufwändig. Auch ist die Weitergabe der Dateien an andere Personen nur nach Anpassung der betreffenden Pfade von Erfolg gekrönt.

Zum Glück gibt es aber einen Ausweg aus diesem Dilemma: parametergesteuerte Abfragen. In diesem Workshop zeige ich Ihnen zwei Möglichkeiten, wie Sie derartige Parameter nutzen können. Die dazugehörigen fertigen Dateien stehen im ZIP-Format (127 KB) zum Download bereit.

Parameter definieren und anwenden

Die Daten mit fiktiven Umsätzen befinden sich in der Datei Daten.xlsx. In meinem Fall ist diese im Pfad D:\Daten gespeichert. Dort soll auch die Auswertung erfolgen.

• Öffnen Sie eine neue leere Mappe.
• Erzeugen Sie eine neue Abfrage: Registerkarte Daten, Abschnitt Daten abrufen und transformieren, Daten abrufen > aus Datei > Aus Arbeitsmappe,
• Nach Auswahl der Datendatei - hier Daten.xlsx - öffnet sich der Navigator, in dem Sie Tabelle1 wählen.
• Klicken Sie auf die Schaltfläche Transformieren, um den PowerQuery-Editor zu öffnen.
• Im Editor öffnen Sie den erweiterten Editor:

• Der erweiterte Editor zeigt alle im Editor vorgenommenen Schritte in der Sprache M an. Wie Sie im Bild sehen können, habe ich die Zeile für die Typänderungen gelöscht und den Text nach in an die vorherige Zeile angepasst. In der Zeile, in der die Quelle definiert ist, steht der absolute Dateipfad. Dies gilt es zu ändern.

• Verlassen Sie den erweiterten Editor und legen im PowerQuery-Editor, Register Start, Abschnitt Parameter, einen neuen Parameter an. Vergeben Sie den Namen Pfad, definieren ihn vom Typ Text und tragen als Aktueller Wert den Pfad der Datei ein (hier D:\Daten\).

• Verlassen Sie den Dialog mit OK und wechseln wieder zum Erweiterten Editor.
• Tauschen Sie die Pfadangabe mit Laufwerk und Ordner aus gegen den Parameternamen Pfad und verketten dahinter den Dateinamen. Die Angabe innerhalb der Klammer lautet demnach: (Pfad & "Daten.xlsx").

• Bestätigen Sie mit Fertig und verlassen den Editor mit Schließen & Laden.

In der Übersicht der Abfragen (Anzeige einschalten im Register Daten) sehen Sie die Parameterdefinition als Verbindung. Hat sich der Dateispeicherort der Datendatei geändert, rufen Sie nach dem Öffnen der Auswertungs-Mappe den PowerQuery-Editor auf und ändern die Paratmeterangabe.

Formelgesteuerte Parameter

Im ersten Beispiel haben wir den Parameter im Dialog eingetragen und damit eine gewisse Dynamik eingebaut.

Im zweiten Beispiel werden Parameter per Formel erzeugt und übernommen, sodass keine manuellen Änderungen im PowerQuery-Editor mehr notwendig sind. Dies macht die Anwendung sicherer, da sich nicht jeder Benutzer der Mappe zwangsläufig mit PowerQuery auskennt und somit Fehler leichter auftreten können.

Zusätzlich zum Dateipfad sollen weitere Parameter zum Einsatz kommen. Damit soll eine Filterung möglich werden, die bereits in PowerQuery abläuft und somit auf Excel-Ebene mit den klassischen Filtermethoden nicht mehr geändert werden kann. Filterung werden soll nach den Spalten Kategorie und Land möglich sein.

• Legen Sie in der Auswertungsmappe ein Tabellenblatt an.
• Erzeugen Sie dort, wie unten zu sehen verschiedene intelligente Tabellen an und bennen diese wie im Screenshot angegeben. Unbedingt diese Namen und Feldbezeichnungen verwenden, da diese später im PowerQuery-Editor verwendet werden. Bei Unklarheit schauen Sie einfach in der fertigen Beispieldatei nach.
• In Zelle B4 wird das Formelergebnis aus Zelle B24 übernommen. Die Zellen B5 (Filterparameter für das Land) und B6 (... für die Kategorie) können entweder manuell vorgegeben werden oder - wie im Beispiel - aus einem anderen Tabellenblatt übernommen. Dadurch ist es später möglich, dieses Tabellenblatt auszublenden.

• Als nächstes wird eine Funktion in PowerQuery erzeugt, die jeweils die benötigten Parameter einliest und in PowerQuery verfügbar macht.
• Erzeugen Sie eine neue leere Abfrage im Registrer Daten, Daten abrufen > Aus anderen Quellen > Leere Abfrage.
• Ändern Sie den Namen der Abfrage in fktSteuerungImport.
• Starten Sie im Power Query-Editor den Erweiterten Editor.
  
• Löschen Sie den gesamten Inhalt aus diesem Editor und fügen stattdessen folgende Formeln ein:
  

(FeldInput as text) =>

let

Quelle = Excel.CurrentWorkbook(){[Name="tbl_SteuerungAbfrage"]}[Content],

Zeile = Table.SelectRows(Quelle, each ([Feld]=FeldInput)),

Inhalt =

if Table.IsEmpty(Zeile)=true

then null

else Record.Field(Zeile{0}, "Wert")

in

Inhalt

let

//Liest alle Daten aus der im Parameter angegebenen Excel-Datei

//Dateipfad wird per Funktion aus der Mappe ermittelt

Verzeichnis = fktSteuerungImport("Dateipfad"),

Quelle = Excel.Workbook(File.Contents(Verzeichnis), null, true),

//Tabelle1 = Name der Tabelle in der Datendatei; ggf. anpassen

Ergebnis = Quelle{[Item="Tabelle1",Kind="Table"]}[Data],

//Land per Funktion wegen Filterung aus Tabelle abfragen

Land_Wahl = fktSteuerungImport("Land"),

//Filter entfernen, wenn "Alle" gewählt wurde, sonst Auswahl (nur 1 Wert)

#"Filter_Land" = if Land_Wahl <> "Alle"

then

Table.SelectRows(#"Ergebnis", each ([Land] = Land_Wahl ))

else

Table.SelectRows(#"Ergebnis", each true),

//Kategorie per Funktion wegen Filterung aus Tabelle abfragen

Kategorie_Wahl = fktSteuerungImport("Kategorie"),

//Filter entfernen, wenn "Alle" gewählt wurde, sonst Auswahl (nur 1 Wert)

#"Filter_Kategorie" = if Kategorie_Wahl <> "Alle"

then

Table.SelectRows(#"Filter_Land", each ([KategorieName] = Kategorie_Wahl ))

else

Table.SelectRows(#"Filter_Land", each true),

//nicht benötigte Spalten entfernen

#"Entfernte Spalten" = Table.RemoveColumns(Filter_Kategorie,{"BestellID", "KundenID", "ArtikelID", "KundenName", "Leitung", "E-Preis", "Anzahl", "Gesamt", "FilialID", "Ort"})

in

#"Entfernte Spalten"