Erstellen einer Parameterabfrage (Power Query)

Excel für Microsoft 365 Excel für das Web Mehr... Weniger

Sie sind möglicherweise mit Parameterabfragen aufgrund ihrer Verwendung in SQL oder Microsoft Query vertraut. Power Query-Parameter weisen jedoch wesentliche Unterschiede auf:

• Parameter können in jedem Abfrageschritt verwendet werden. Zusätzlich zur Funktion als Datenfilter können Parameter verwendet werden, um Dinge wie 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 aber getrennt von den Datenabfragen, in denen sie verwendet werden. Nach der Erstellung können Sie bei Bedarf einen Parameter zu Abfragen hinzufügen.

Hinweis Wenn Sie Parameterabfragen auf andere Weise erstellen möchten, finden Sie weitere Informationen unter Erstellen einer Parameterabfrage in Microsoft Query.

Sie können einen Parameter verwenden, um einen Wert in einer Abfrage automatisch zu ändern und vermeiden, die Abfrage jedes Mal zu bearbeiten, um den Wert zu ändern. Sie ändern nur den Parameterwert. Sobald Sie einen Parameter erstellt haben, wird er in einer speziellen Parameterabfrage gespeichert, die Sie bequem direkt aus Excel ändern können.

1. Wählen Sie Daten > Daten abrufen > Andere Quellen > Power Query-Editor starten aus .

2. Wählen Sie im Power Query-Editor Start > Parameter verwalten > Neue Parameter aus.

3. Wählen Sie im Dialogfeld „ Parameter verwalten " die Option „ Neu " aus.

4. Stellen Sie nach Bedarf Folgendes ein:

   Name	Diese sollte die Funktion des Parameters widerspiegeln, aber so kurz wie möglich halten.
   Beschreibung	Dies kann alle Details enthalten, die den Benutzern helfen, den Parameter richtig zu verwenden.
   Erforderlich	Führen Sie einen der folgenden Schritte aus: Beliebiger Wert Sie können in der Parameterabfrage einen beliebigen Wert eines beliebigen Datentyps eingeben. Werteliste Sie können die Werte auf eine bestimmte Liste einschränken, indem Sie sie in das kleine Raster eingeben. Sie müssen auch unten einen Standardwert und einen aktuellen Wert auswählen. Abfrage Wählen Sie eine Listenabfrage aus, die einer durch Kommas getrennten und in geschweiften Klammern eingeschlossenen strukturierten Spalte einer Liste ähnelt. Beispielsweise könnte ein Statusfeld für Probleme drei Werte haben: {"Neu", "Laufend", "Geschlossen"}. Sie müssen die Listenabfrage vorher erstellen, indem Sie den erweiterten Editor öffnen (Start > Erweiterter Editor auswählen), die Codevorlage entfernen, die Werteliste im Abfragelistenformat eingeben und dann Fertig auswählen. Nachdem Sie den Parameter erstellt haben, wird die Listenabfrage in Ihren Parameterwerten angezeigt.
   Typ	Dies gibt den Datentyp des Parameters an.
   Vorgeschlagene Werte	Fügen Sie bei Bedarf eine Liste mit 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 „ Werteliste " festgelegt ist, und gibt an, welches Listenelement der Standardwert ist. In diesem Fall müssen Sie einen Standardwert 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 Aktueller Wert nicht leer sein.

5. Um den Parameter zu erstellen, wählen Sie OK .

Hier ist eine Möglichkeit, Änderungen an Datenquellenspeicherorten zu verwalten und Aktualisierungsfehler zu vermeiden. Wenn Sie beispielsweise ein ähnliches Schema und eine ähnliche Datenquelle annehmen, 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 wandert in einen anderen Ordner, oder Sie müssen einfach zwischen einer Entwicklungs-/Test-/Produktionsumgebung wechseln.

Schritt 1: Erstellen Sie eine Parameterabfrage

Im folgenden Beispiel haben Sie mehrere CSV-Dateien, die Sie mithilfe des Vorgangs zum Importieren von Ordnern (Daten auswählen > Daten abrufen > Aus Dateien > Aus Ordner ) aus dem Ordner C:\ DataFilesCSV1 importieren. Aber manchmal wird gelegentlich ein anderer Ordner als Speicherort zum Ablegen der Dateien verwendet, C:\DataFilesCSV2. Sie können einen Parameter in einer Abfrage als Ersatzwert für den anderen Ordner verwenden.

1. Wählen Sie Start > Parameter verwalten > Neuer Parameter aus .

2. Geben Sie im Dialogfeld „ Parameter verwalten " die folgenden Informationen ein:

   Name	CSVFileDrop
   Beschreibung	Alternativer Ablageort für Dateien
   Erforderlich	Ja
   Typ	Text
   Vorgeschlagene Werte	Jeder Wert
   Aktueller Wert	C:\DataFilesCSV1

3. Wählen Sie OK aus .

Schritt 2: Fügen Sie den Parameter der Datenabfrage hinzu

1. Um den Ordnernamen als Parameter festzulegen, wählen Sie in Abfrageeinstellungen unter Abfrageschritte Quelle und dann Einstellungen bearbeiten aus.

2. Stellen Sie sicher, dass die Option Dateipfad auf Parameter eingestellt ist, und wählen Sie dann den Parameter, den Sie gerade erstellt haben, aus der Dropdown-Liste aus.

3. Wählen Sie OK aus .

Schritt 3: Aktualisieren Sie den Parameterwert

Der Speicherort des Ordners wurde gerade geändert, sodass Sie jetzt einfach die Parameterabfrage aktualisieren können.

1. Wählen Sie Daten > Verbindungen und Abfragen > Registerkarte Abfragen aus, klicken Sie mit der rechten Maustaste auf die Parameterabfrage, und wählen Sie dann Bearbeiten aus.

2. Geben Sie den neuen Speicherort in das Feld Aktueller Wert ein, z. B. C:\DataFilesCSV2 .

3. Wählen Sie Startseite > Schließen und laden aus .

4. Um Ihre Ergebnisse zu bestätigen, fügen Sie der Datenquelle neue Daten hinzu und aktualisieren Sie dann die Datenabfrage mit dem aktualisierten Parameter ( Daten auswählen > Alle aktualisieren ).

Manchmal möchten Sie den Filter einer Abfrage auf einfache Weise ändern, um andere Ergebnisse zu erhalten, ohne entweder die Abfrage zu bearbeiten oder leicht unterschiedliche Kopien derselben Abfrage zu erstellen. In diesem Beispiel ändern wir ein Datum, um bequem einen Datenfilter zu ändern.

1. Suchen Sie zum Öffnen einer Abfrage eine zuvor aus dem Power Query-Editor geladene, 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 .

2. Wählen Sie den Filterpfeil in einer beliebigen Spaltenüberschrift aus, um Ihre Daten zu filtern, und wählen Sie dann einen Filterbefehl aus, z. B. Datums-/Uhrzeitfilter > Nach . Das Dialogfeld „ Zeilen filtern" wird angezeigt.
   
   

3. Wählen Sie die Schaltfläche links neben dem Feld „ Wert " und führen Sie dann einen der folgenden Schritte aus:

   • Um einen vorhandenen Parameter zu verwenden, wählen Sie Parameter und dann den gewünschten Parameter aus der rechts angezeigten Liste aus.

   • Um einen neuen Parameter zu verwenden, wählen Sie Neuer Parameter und erstellen Sie dann einen Parameter.

4. Geben Sie das neue Datum in das Feld Aktueller Wert ein und wählen Sie dann Start > Schließen und laden aus.

5. Um Ihre Ergebnisse zu bestätigen, fügen Sie der Datenquelle neue Daten hinzu und aktualisieren Sie dann die Datenabfrage mit dem aktualisierten Parameter ( Daten auswählen > Alle aktualisieren ). Ändern Sie beispielsweise den Filterwert auf ein anderes Datum, um neue Ergebnisse anzuzeigen.

6. Geben Sie das neue Datum in das Feld Aktueller Wert ein.

7. Wählen Sie Startseite > Schließen und laden aus .

8. Um Ihre Ergebnisse zu bestätigen, fügen Sie der Datenquelle neue Daten hinzu und aktualisieren Sie dann die Datenabfrage mit dem aktualisierten Parameter ( Daten auswählen > Alle aktualisieren ).

In diesem Beispiel wird der Wert im Abfrageparameter aus einer Zelle in Ihrer Arbeitsmappe gelesen. Sie müssen die Parameterabfrage nicht ändern, Sie aktualisieren lediglich den Zellenwert. Sie möchten beispielsweise eine Spalte nach dem Anfangsbuchstaben filtern, aber den Wert einfach in einen beliebigen Buchstaben von A bis Z ändern.

1. Erstellen Sie auf dem Arbeitsblatt in einer Arbeitsmappe, in der die zu filternde Abfrage geladen ist, eine Excel-Tabelle mit zwei Zellen: einer Kopfzeile und einem Wert.
   

   MeinFilter
   G

2. Wählen Sie eine Zelle in der Excel-Tabelle aus, und wählen Sie dann Daten > Daten abrufen > Aus Tabelle/Bereich aus. Der Power Query-Editor wird angezeigt.

3. Ändern Sie im Feld Name des Bereichs Abfrageeinstellungen auf der rechten Seite den Abfragenamen in einen aussagekräftigeren Namen, z. B. FilterCellValue .

4. 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 aus.

   Beachten Sie, dass sich die Formel geändert hat zu = #"Changed Type"{0}[MyFilter]

   Wenn Sie in Schritt 10 die Excel-Tabelle als Filter verwenden, verweist Power Query auf den Tabellenwert als Filterbedingung. Ein direkter Verweis auf die Excel-Tabelle würde einen Fehler verursachen.

5. Wählen Sie Start > Schließen und laden > Schließen und laden nach aus . Sie haben jetzt einen Abfrageparameter namens "FilterCellValue", den Sie in Schritt 12 verwenden.

6. Wählen Sie im Dialogfeld „ Daten importieren" die Option „ Nur Verbindung erstellen " und dann „ OK " aus.

7. Öffnen Sie die Abfrage, die Sie mit dem Wert in der FilterCellValue-Tabelle filtern möchten, die zuvor aus dem Power Query-Editor geladen wurde, indem Sie eine Zelle in den Daten auswählen und dann Abfrage > Bearbeiten auswählen. Weitere Informationen finden Sie unter Erstellen, Laden oder Bearbeiten einer Abfrage in Excel .

8. Wählen Sie den Filterpfeil in einer beliebigen Spaltenüberschrift aus, um Ihre Daten zu filtern, und wählen Sie dann einen Filterbefehl aus, z. B. Textfilter > Beginnt mit . Das Dialogfeld „ Zeilen filtern" wird angezeigt.

9. 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 Tabelle FilterCellValue, den Sie im nächsten Schritt eingeben.

10. Wählen Sie den Pfeil auf der rechten Seite der Formelleiste aus, um die gesamte Formel anzuzeigen. Hier ist ein Beispiel für eine Filterbedingung in einer Formel:
    
    = Table.SelectRows(#"Changed Type", jeweils Text.StartsWith([Name], "G"))

11. Wählen Sie den Wert des Filters aus. Wählen Sie in der Formel "G".

12. Geben Sie mit M Intellisense die ersten paar Buchstaben der FilterCellValue-Tabelle ein, die Sie erstellt haben, und wählen Sie sie dann aus der angezeigten Liste aus.

13. Wählen Sie Start > Schließen > Schließen und laden aus .

Ergebnis

Ihre Abfrage verwendet jetzt den Wert in der von Ihnen erstellten Excel-Tabelle, um die Abfrageergebnisse zu filtern. Um einen neuen Wert zu verwenden, bearbeiten Sie den Zelleninhalt in der ursprünglichen Excel-Tabelle in Schritt 1, ändern Sie „G" in „V" und aktualisieren Sie dann die Abfrage.

Sie können steuern, ob Parameterabfragen erlaubt oder nicht erlaubt sind.

1. Wählen Sie im Power Query-Editor Datei > Optionen und Einstellungen > Abfrageoptionen > Power Query-Editor aus.

2. Wählen Sie im linken Bereich unter GLOBAL die Option Power Query Editor aus.

3. Aktivieren oder deaktivieren Sie im Bereich auf der rechten Seite unter Parameter die Option Parametrisierung in Datenquellen- und Transformationsdialogen immer zulassen .

Siehe auch

Power Query für Excel-Hilfe

Abfrageparameter verwenden (docs.com)