Parameter in Power Query

Parameter in Power Query machen das Datenmodell wartbarer - beispielsweise bei der Änderung von Verzeichnissen, Umschalten von Test auf Echtbetrieb oder Umzug einer Datenbank. Wie erläutern wie man mit Parametern umgeht.

Lokale Dateien als Datenquellen

Viele unserer Daten stammen aus lokalen Dateien - nicht die beste Variante, aber man kommt darum selten herum. Bei der Entwicklung verweisen wir auf 20 verschiedene Dateien auf unserer Festplatte. Jetzt sind wir fertig und möchten unseren Kollegen die Datei übergeben. Dazu verschieben wir die Quelldateien auf ein Netzlaufwerk. Und wir müssen alle Abfragen ändern.

Oder:

Zu Corona-Zeiten haben Sie viel von zu Hause gearbeitet. Ihr Zugang zur Firma läuft via VPN (Virtual Private Network). Je nach Größe der Dateien, Geschwindigkeit Ihrer Internetverbindung und Technologie des VPN kann es vorkommen, dass Sie zu häufig Kaffee holen während Sie warten müssen... Daher empfehle ich immer, die Dateien während der Entwicklung auf die lokale Festplatte zu kopieren. Dann müssen Sie aber wieder alle Datenquellen in Ihren Abfragen ändern.

Das Basisverzeichnis

Sicherlich befinden sich Ihre Dateien in einer Struktur. Ausgehend von einem Verzeichnis sind Ihre Dateien beispielsweise in folender Struktur:

• /Stammdaten/
• /Sales/
• /Forecasting/

Wenn man jetzt Basisverzeichnis an einer zentralen Stelle ändern könnte, wäre ein Umzug ein Kinderspiel.

Parameter in Power Query

Parameter in Power Query gab es schon für Excel Power Query. Im Abfrage Editor finden Sie die Parameter hier:

Es öffnet sich ein Dialog, in dem Sie beliebig viele Parameter über den kleinen Hyperlink Neu in der oberen Mitte anlegen können:

Neuer Parameter

Ein neuer Parameter benötigt

• einen Namen: den Sie später in den Abfragen nutzen - beispielsweise "Basisverzeichnis"
• eine Beschreibung: (tragen Sie hier ein, wozu der Parameter angelegt wurde)
• eine Pflichtfeld Info: falls der Parameter einen Wert haben MUSS. Im Beispiel muss der Parameter gesetzt sein.
• einen Typ: Der Datentyp des Parameters. Für unser Basisverzeichnis nutzen wir "Text"
• etwaige vorgeschlagene Werte: Das benötigen wir nicht. Hier kann man aber Werte hinterlegen (beispielsweise PROD und TEST) oder sogar aus einer Abfrage dynamisch Werte vorschlagen lassen
• einen aktuellen Wert: Hier hinterlegen wir den aktuellen Wert - also unser aktuelles Basisverzeichnis. Ist Pflichtfeld aktiviert, MUSS hier ein Wert stehen

Parameter in den Abfragen

Ein neuer Parameter wird auch in den Abfragen angezeigt. Von dort aus kann man diesen direkt verändern ohne die Parameterverwaltung öffnen zu müssen:

Nutzen der Parameter

Bei manchen Datenquellen kann man direkt in den entsprechenden Dialogen auf die Parameter zurückgreifen. Beispielsweise die Datenquelle Folder, wenn man mehrere Dateien aus einem Ordner lesen möchte. Statt der Angabe eines Verzeichnisses wird ein Parameter gewählt:

Existieren die Abfragen bereits, kann man diese im erweiterten Editor abändern. Hier als Beispiel eine Abfrage auf eine Excel-Datei, die schon existiert hat: Hier tauschen wir in der Datenquelle den Teil des Pfades aus, der zukünftig vom Parameter Basisverzeichnis bestimmt wird:

Pfad-Trennzeichen

Gerade bei Verzeichnissen muss man Acht geben, ob im Parameter am Schluss ein \ vorkommt oder nicht. Entweder man schreibt das einfach in den Bemerkungstext (Achtung: Verzeichnis muss mit einem Pfadtrennzeichen enden) oder man entwickelt eine benutzerdefinierte Funktion, die die Teile des Pfades korrekt verknüpft. Eine solche Funktion finden Sie übrigens auf powerqueryformatter.com bei den Snippets unter dem Namen File_CombinePathInfos.