Sie sind möglicherweise mit Parameterabfragen mit ihrer Verwendung in SQL oder Microsoft Query vertraut. Die Power Query-Parameter weisen jedoch wesentliche Unterschiede auf:
- Parameter können in jedem Abfrageschritt verwendet werden. Parameter dienen nicht nur als Datenfilter, sondern können auch verwendet werden, um beispielsweise einen Dateipfad oder einen Servernamen anzugeben.
- Parameter fordern nicht zur Eingabe auf. Stattdessen können Sie ihren Wert mithilfe von Power Query schnell ändern. Sie können sogar die Werte aus Zellen in Excel speichern und abrufen.
- Parameter werden in einer einfachen Parameterabfrage gespeichert, sind jedoch von den Datenabfragen getrennt, in denen sie verwendet werden. Nach der Erstellung können Sie Abfragen nach Bedarf einen Parameter hinzufügen.
Hinweis Wenn Sie die andere Möglichkeit zum Erstellen von Parameterabfragen wünschen, finden Sie weitere Informationen unter Erstellen einer Parameterabfrage in Microsoft Query.
Parameter erstellen
Sie können einen Parameter verwenden, um einen Wert in einer Abfrage automatisch zu ändern, und vermeiden, dass die Abfrage jedes Mal bearbeitet wird, um den Wert zu ändern. Sie ändern einfach den Parameterwert. Sobald Sie einen Parameter erstellt haben, wird er in einer speziellen Parameterabfrage gespeichert, die Sie bequem direkt aus Excel heraus ändern können.
Daten>auswählen Daten> abrufenAndere Quellen>Starten Sie den Power Query-Editor.
Wählen Sie im Power Query-Editor Startseite>Parameter verwalten > Neue Parameter.
Klicken Sie im Dialogfeld Parameter verwalten auf Neu.
Legen Sie bei Bedarf Folgendes fest:
Name Dies sollte die Funktion des Parameters widerspiegeln, aber halten Sie ihn so kurz wie möglich. Beschreibung Dieser Code kann alle Details enthalten, die zur korrekten Verwendung des Parameters beitragen. Erforderlich Führen Sie eine der folgenden Aktionen aus:
Beliebiger Wert Sie können einen beliebigen Wert eines beliebigen Datentyps in die Parameterabfrage eingeben.
Liste der Werte Sie können die Werte auf eine bestimmte Liste begrenzen, indem Sie sie in das kleine Raster eingeben. Sie müssen außerdem einen Standardwert und einen aktuellen Wert unten auswählen.
Abfrage Wählen Sie eine Listenabfrage aus, die einer strukturierten Listenspalte ähnelt, die durch Kommas getrennt und in geschweifte Klammern eingeschlossen ist.
Beispielsweise kann das Feld "Status von Problemen" drei Werte aufweisen: {"Neu", "Laufend", "Geschlossen"}. Sie müssen die Listenabfrage zuvor erstellen, indem Sie die Erweiterter Editor öffnen (wählen Sie Start>aus Erweiterter Editor), die Codevorlage entfernen, die Werteliste im Abfragelistenformat eingeben und dann Fertig auswählen.
Nachdem Sie die Erstellung des Parameters abgeschlossen haben, wird die Listenabfrage in Ihren Parameterwerten angezeigt.F Hier wird der Datentyp des Parameters angegeben. Vorgeschlagene Werte Fügen Sie bei Bedarf eine Liste von Werten hinzu, oder geben Sie eine Abfrage an, um Vorschläge für die Eingabe bereitzustellen. Standardwert Dies wird nur angezeigt, wenn "Vorgeschlagene Werte " auf "Liste von Werten" festgelegt ist und das Listenelement als Standard angegeben wird. In diesem Fall müssen Sie eine Standardeinstellung auswählen. Aktueller Wert Je nachdem, wo Sie den Parameter verwenden, gibt die Abfrage möglicherweise keine Ergebnisse zurück, wenn dieser leer ist. Wenn Erforderlich ausgewählt ist, darf der aktuelle Wert nicht leer sein. Wählen Sie zum Erstellen des Parameters OK aus.
Verwenden eines Parameters zum Ändern einer Datenquelle
Hier ist eine Möglichkeit, Änderungen an Datenquellenspeicherorten zu verwalten und Aktualisierungsfehler zu vermeiden. Wenn beispielsweise ein ähnliches Schema und eine ähnliche Datenquelle vorliegen, erstellen Sie einen Parameter, um eine Datenquelle einfach zu ändern und Fehler bei der Datenaktualisierung zu vermeiden. Manchmal ändert sich der Server, die Datenbank, der Ordner, der Dateiname oder der Speicherort. Vielleicht tauscht ein Datenbankmanager gelegentlich einen Server aus, ein monatlicher Drop von CSV-Dateien wird in einen anderen Ordner verschoben, oder Sie müssen einfach zwischen einer Entwicklungs-/Test-/Produktionsumgebung wechseln.
Schritt 1: Erstellen einer Parameterabfrage
Im folgenden Beispiel haben Sie mehrere CSV-Dateien, die Sie mithilfe des Ordnerimportvorgangs (Daten>auswählen, Daten>aus Ordner abrufen aus Files>From Folder) aus dem Ordner C:\DataFilesCSV1 importieren. Aber manchmal wird gelegentlich ein anderer Ordner als Speicherort für die Dateien verwendet, C:\DataFilesCSV2. Sie können einen Parameter in einer Abfrage als Ersatzwert für den anderen Ordner verwenden.
Start> auswählenParameter> verwaltenNeuer Parameter.
Geben Sie im Dialogfeld Parameter verwalten die folgenden Informationen ein:
Name CSVFileDrop Beschreibung Alternativer Speicherort für Dateiablagen Erforderlich Ja F Text Vorgeschlagene Werte Beliebiger Wert Aktueller Wert C:\DataFilesCSV1 Wählen Sie OK aus.
Schritt 2: Hinzufügen des Parameters zur Datenabfrage
- Um den Ordnernamen als Parameter festzulegen, wählen Sie unter Abfrageeinstellungen unter Abfrageschritte die Option Quelle und dann Einstellungen bearbeiten aus.
- Stellen Sie sicher, dass die Option Dateipfad auf Parameter festgelegt ist, und wählen Sie dann den soeben erstellten Parameter aus der Dropdownliste aus.
- Wählen Sie OK aus.
Schritt 3: Aktualisieren des Parameterwerts
Der Speicherort des Ordners hat sich gerade geändert, sodass Sie jetzt einfach die Parameterabfrage aktualisieren können.
- Wählen Sie auf der Registerkarte Datenverbindungen>& Abfragen>aus , klicken Sie mit der rechten Maustaste auf die Parameterabfrage, und wählen Sie dann Bearbeiten aus.
- Geben Sie den neuen Speicherort in das Feld "Aktueller Wert " ein, z. B. "C:\DataFilesCSV2".
- Start> auswählenSchließen & Laden.
- Um Ihre Ergebnisse zu bestätigen, fügen Sie der Datenquelle neue Daten hinzu, und aktualisieren Sie dann die Datenabfrage mit dem aktualisierten Parameter ("Alle aktualisieren").>
Verwenden eines Parameters zum Filtern von Daten
Manchmal möchten Sie den Filter einer Abfrage einfach ändern, um unterschiedliche Ergebnisse zu erhalten, ohne die Abfrage zu bearbeiten oder geringfügig unterschiedliche Kopien derselben Abfrage zu erstellen. In diesem Beispiel ändern wir ein Datum, um einen Datenfilter bequem zu ändern.
Um eine Abfrage zu öffnen, suchen Sie eine, die zuvor aus dem Power Query-Editor geladen wurde, wählen Sie eine Zelle in den Daten aus, und wählen Sie dann Abfrage>bearbeiten aus. Weitere Informationen finden Sie unter Erstellen, Laden oder Bearbeiten einer Abfrage in Excel.
Klicken Sie auf den Filterpfeil in einer beliebigen Spaltenüberschrift, um Ihre Daten zu filtern, und wählen Sie dann einen Filterbefehl aus, z. B. "Datums-/Uhrzeitfilter>nach". Das Dialogfeld Zeilen filtern wird angezeigt.
Wählen Sie die Schaltfläche links neben dem Feld "Wert " aus, und führen Sie dann einen der folgenden Schritte aus:
- Wenn Sie einen vorhandenen Parameter verwenden möchten, wählen Sie "Parameter" und dann in der Liste auf der rechten Seite den gewünschten Parameter aus.
- Wenn Sie einen neuen Parameter verwenden möchten, wählen Sie "Neuer Parameter" aus, und erstellen Sie dann einen Parameter.
Geben Sie das neue Datum in das Feld "Aktueller Wert " ein, und klicken Sie dann auf "Start>Schließen & Laden".
Um Ihre Ergebnisse zu bestätigen, fügen Sie der Datenquelle neue Daten hinzu, und aktualisieren Sie dann die Datenabfrage mit dem aktualisierten Parameter ("Alle aktualisieren").> Ändern Sie beispielsweise den Filterwert in ein anderes Datum, um neue Ergebnisse anzuzeigen.
Geben Sie das neue Datum in das Feld "Aktueller Wert " ein.
Start> auswählenSchließen & Laden.
Um Ihre Ergebnisse zu bestätigen, fügen Sie der Datenquelle neue Daten hinzu, und aktualisieren Sie dann die Datenabfrage mit dem aktualisierten Parameter ("Alle aktualisieren").>
Verwenden eines Zellwerts zum Filtern von Daten
In diesem Beispiel wird der Wert im Abfrageparameter aus einer Zelle in Ihrer Arbeitsmappe gelesen. Sie müssen die Parameterabfrage nicht ändern, sondern aktualisieren nur den Zellwert. Sie möchten z. B. eine Spalte nach dem ersten Buchstaben filtern, den Wert aber einfach in einen beliebigen Buchstaben von A bis Z ändern.
Erstellen Sie in einer Arbeitsmappe eine Excel-Tabelle mit zwei Zellen: einer Kopfzeile und einem Wert.
MyFilter G Wählen Sie eine Zelle in der Excel-Tabelle und dann "Daten> Datenaus Tabelle/Bereichabrufen>" aus. Der Power Query-Editor wird angezeigt.
Ändern Sie im Feld Name des Bereichs Abfrageeinstellungen auf der rechten Seite den Abfragenamen in einen aussagekräftigeren Namen, z. B. FilterCellValue.
Um den Wert in der Tabelle und nicht die Tabelle selbst zu übergeben, klicken Sie mit der rechten Maustaste auf den Wert in der Datenvorschau, und wählen Sie dann Drilldown ausführen.
Beachten Sie, dass die Formel in= #"Changed Type"{0}[MyFilter]
Wenn Sie die Excel-Tabelle in Schritt 10 als Filter verwenden, verweist Power Query als Filterbedingung auf den Tabellenwert. Ein direkter Verweis auf die Excel-Tabelle würde einen Fehler verursachen.Wählen Sie Start>Schließen & Laden>Schließen & Laden nach. Sie haben jetzt einen Abfrageparameter namens "FilterCellValue", den Sie in Schritt 12 verwenden.
Klicken Sie im Dialogfeld "Daten importieren" auf "Nur Verbindung erstellen", und klicken Sie dann auf "OK".
Öffnen Sie die Abfrage, die Sie filtern möchten, mit dem Wert in der FilterCellValue-Tabelle, die zuvor aus dem Power Query-Editor geladen wurde, indem Sie eine Zelle in den Daten auswählen und dann Abfragebearbeitung> auswählen. Weitere Informationen finden Sie unter Erstellen, Laden oder Bearbeiten einer Abfrage in Excel.
Klicken Sie auf den Filterpfeil in einer beliebigen Spaltenüberschrift, um Ihre Daten zu filtern, und wählen Sie dann einen Filterbefehl aus, z. B. Textfilter>beginnt mit. Das Dialogfeld Zeilen filtern wird angezeigt.
Geben Sie einen beliebigen Wert in das Feld Wert ein, z. B. "G", und wählen Sie dann "OK" aus. In diesem Fall ist der Wert ein temporärer Platzhalter für den Wert in der FilterCellValue-Tabelle, die Sie im nächsten Schritt eingeben.
Wählen Sie den Pfeil auf der rechten Seite der Bearbeitungsleiste aus, um die gesamte Formel anzuzeigen. Hier ein Beispiel für eine Filterbedingung in einer Formel:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Wählen Sie den Wert des Filters aus. Wählen Sie in der Formel "G" aus.
Geben Sie mit M Intellisense die ersten Buchstaben der von Ihnen erstellten Tabelle FilterCellValue ein, und wählen Sie sie dann aus der angezeigten Liste aus.
Start> auswählenSchließen>Schließen & Laden.
Ergebnis
Ihre Abfrage verwendet jetzt den Wert in der Excel-Tabelle, die Sie zum Filtern der Abfrageergebnisse erstellt haben. Um einen neuen Wert zu verwenden, bearbeiten Sie den Zellinhalt in der ursprünglichen Excel-Tabelle in Schritt 1, ändern Sie "G" in "V", und aktualisieren Sie dann die Abfrage.
Verwendung von Parameterabfragen steuern
Sie können steuern, ob Parameterabfragen zulässig oder nicht zulässig sind.
- Wählen Sie im Power Query-Editor Dateioptionen>und Einstellungen>AbfrageoptionenPower>Query-Editor aus.
- Wählen Sie im linken Bereich unter GLOBALdie Option Power Query-Editor aus.
- Aktivieren oder deaktivieren Sie im rechten Bereich unter ParameterParametrisierung in Datenquellen- und Transformationsdialogfeldern immer zulassen.