Allein mit dem Power Query-Editor haben Sie bereits Power Query-Formeln erstellt. Schauen Sie sich an, wie Power Query funktioniert, indem Sie im Verdeck sehen. Sie erfahren, wie Sie Formeln aktualisieren oder hinzufügen, indem Sie sich den Power Query-Editor in Aktion anschauen.  Mit dem Erweiterten Editor können Sie sogar eigene Formeln erstellen.           

Der Power Query-Editor bietet eine Erfahrung mit Datenabfragen und -Excel, mit der Sie Daten aus vielen Datenquellen neu gestalten können. Zum Anzeigen des Fensters Power Query-Editor importieren Sie Daten aus externen Datenquellen ineinem Excel-Arbeitsblatt, markieren Sie eine Zelle in den Daten, und wählen Sie dann Abfrage > Bearbeiten aus. Im Folgenden finden Sie eine Zusammenfassung der wichtigsten Komponenten.

Teile des Abfrage-Editors

  1. Das Menüband des Power Query-Editors, mit dem Sie Ihre Daten gestalten können

  2. Der Bereich "Abfragen", in dem Sie nach Datenquellen und Tabellen suchen können

  3. Kontextmenüs, die praktische Verknüpfungen zu Befehlen im Menüband sind

  4. Die Datenvorschau, in der die Ergebnisse der auf die Daten angewendeten Schritte angezeigt werden

  5. Bereich "Einstellungen", in dem die Eigenschaften und die einzelnen Schritte in der Abfrage aufgelistet sind

Hinter den Kulissen basiert jeder Schritt in einer Abfrage auf einer Formel, die in der Formelleiste angezeigt wird.

Beispiel für eine Abfrage-Editor-Formel

Es kann auch sein, dass Sie eine Formel ändern oder erstellen möchten. Formeln verwenden die Power Query-Formelsprache, mit der Sie sowohl einfache als auch komplexe Ausdrücke erstellen können. Weitere Informationen zur Syntax, zu Argumenten, Anmerkungen, Funktionen und Beispielen finden Sie unter Power Query M-Formelsprache.

Verwenden Sie Power Query beispielsweise bei einer Liste von Fußballmeisterschaften, um Rohdaten, die Sie auf einer Website gefunden haben, zu übernehmen und in eine gut formatierte Tabelle zu verwandeln. Schauen Sie sich an, wie Abfrageschritte und entsprechende Formeln für jede Aufgabe im Bereich Abfrage Einstellungen unter Angewendete Schritte und in der Formelleiste erstellt werden.

Ihr Browser unterstützt kein Video.

Vorgehensweise

  1. Zum Importieren der Daten wählen Sie> aus dem Webaus, geben Sie "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" in das Feld URL ein, und wählen Sie dann OK aus.

  2. Wählen Sie im Dialogfeld Navigator links die Tabelle Ergebnisse [Bearbeiten] aus, und wählen Sie dann unten Daten transformieren aus. Der Power Query-Editor wird angezeigt.

  3. Wenn Sie den Standardabfragenamen ändern möchten, löschen Sie im Bereich Abfrage Einstellungen unter Eigenschaften denText "Ergebnisse [Bearbeiten]", und geben Sie dann " NAVIGATIONs" ein.

  4. Um unerwünschte Spalten zu entfernen, markieren Sie die erste, vierte und fünfte Spalte, und wählen Sie dann Start > Spalte entfernen > Andere Spalten entfernen aus.

  5. Um unerwünschte Werte zu entfernen, wählen Sie Spalte1, wählen Sie Start> Werte ersetzen ,geben Sie "Details" in das Feld Zu findende Werte ein, und wählen Sie dann OK aus.

  6. Wenn Sie Zeilen entfernen möchten, in die das Wort "Jahr" steht, wählen Sie den Filterpfeil in Spalte1aus, aktivieren Sie das Kontrollkästchen neben "Jahr", und wählen Sie dann OK aus.

  7. Um die Spaltenüberschriften umzubenennen, doppelklicken Sie darauf, und ändern Sie dann "Spalte1" in "Jahr", "Spalte4" in "Gewinner" und "Spalte5" in "Endergebnis".

  8. Um die Abfrage zu speichern, wählen Sie Start> schließen & Laden aus.

Ergebnis

Ergebnisse der exemplarischen Vorgehensweise – die ersten Zeilen

Die folgende Tabelle enthält eine Zusammenfassung der einzelnen angewendeten Schritte und der entsprechenden Formel.

Abfrageschritt und -aufgabe

Formel

Quelle

Herstellen einer Verbindung mit einer Webdatenquelle

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navigation

Auswählen der Tabelle für die Verbindung

=Source{2}[Data]

Geänderter Typ

Ändern von Datentypen (was von Power Query automatisch ausgeführt wird)

= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})

Andere Spalten entfernt

Entfernen anderer Spalten, um nur relevante Spalten anzuzeigen

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Ersetzter Wert

Ersetzen von Werten zum Bereinigen von Werten in einer ausgewählten Spalte

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Gefilterte Zeilen

Filtern von Werten in einer Spalte

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Umbenannte Spalten

Spaltenüberschriften wurden so geändert, dass sie aussagekräftig sind

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Wichtig    Achten Sie beim Bearbeiten der Schritte "Quelle", "Navigation"und "Geänderter Typ" darauf, dass sie von Power Query erstellt werden, um die   Datenquelle zu definieren und zu einrichten.

Ein- oder Ausblenden der Formelleiste

Die Formelleiste wird standardmäßig angezeigt, aber wenn sie nicht sichtbar ist, können Sie sie wieder anzeigen.

  • Wählen Sie Ansicht > Layout >der Formelleiste aus.

Dit-Formeln in der Formelleiste

  1. Um eine Abfrage zu öffnen, suchen Sie eine zuvor aus dem Power Query-Editor geladene Abfrage, 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 im Einstellungen Abfrageabfrage unter Angewendete Schritteden Schritt aus, den Sie bearbeiten möchten.

  3. Suchen und ändern Sie in der Formelleiste die Parameterwerte, und wählen Sie dann das Symbol Eingabetaste Das Eingabesymbol links neben der Formelleiste in Power Query aus, oder drücken Sie die EINGABETASTE. Ändern Sie z. B. diese Formel so, dass auch Spalte2:

    Vor: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    Nach:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Wählen Sie das Symbol Das Eingabesymbol links neben der Formelleiste in Power Query ein, oder drücken Sie die EINGABETASTE, um die neuen Ergebnisse in der Datenvorschau anzuzeigen.

  5. Wenn Sie das Ergebnis in einem Excel anzeigen möchten, wählen Sie Start> schließen & Laden aus.

Erstellen einer Formel in der Formelleiste

Bei einem einfachen Formelbeispiel wandeln wir einen Textwert mithilfe der Funktion Text.Properin die entsprechende Hülle um.

  1. Um eine leere Abfrage zu öffnen, wählen Sie in Excel die Option Daten >Daten > Aus anderen Quellen > Leere Abfrage aus. Weitere Informationen finden Sie unter Erstellen, Laden oder Bearbeiten einer Abfrage in Excel.

  2. Geben Sie in der Formelleiste=Text.Proper("text value")ein, und wählen Sie dann das Symbol Eingabetaste Das Eingabesymbol links neben der Formelleiste in Power Query aus, oder drücken Sie die EINGABETASTE.

    Die Ergebnisse werden in der Datenvorschau angezeigt.

  3. Wenn Sie das Ergebnis in einem Excel anzeigen möchten, wählen Sie Start> schließen & Laden aus.

Ergebnis:

Text.Proper

 Wenn Sie eine Formel erstellen, überprüft Power Query die Formelsyntax. Wenn Sie jedoch einen Zwischenschritt in einer Abfrage einfügen, neu anordnen oder löschen, kann es sein, dass eine Abfrage nicht mehr besteht.  Überprüfen Sie immer die Ergebnisse in der Datenvorschau.

Wichtig    Achten Sie beim Bearbeiten der Schritte "Quelle", "Navigation"und "Geänderter Typ" darauf, dass sie von Power Query erstellt werden, um die   Datenquelle zu definieren und zu einrichten.

Bearbeiten einer Formel mithilfe eines Dialogfelds

Bei dieser Methode werden Dialogfelder verwendet, die sich je nach Schritt unterscheiden. Sie müssen die Syntax der Formel nicht kennen.

  1. Um eine Abfrage zu öffnen, suchen Sie eine zuvor aus dem Power Query-Editor geladene Abfrage, 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 im Bereich AbfrageEinstellungen unter Angewendete Schritte das Symbol Einstellungen Symbol "Einstellungen" bearbeiten des Schritts aus, den Sie bearbeiten möchten, oder klicken Sie mit der rechten Maustaste auf den Schritt, und wählen Sie dann Einstellungen aus.

  3. Nehmen Sie im Dialogfeld die Änderungen vor, und wählen Sie OK aus.

Einfügen eines Schritts

Nachdem Sie einen Abfrageschritt abgeschlossen haben, mit dem Ihre Daten neu gestaltet werden, wird unterhalb des aktuellen Abfrageschritts ein Abfrageschritt hinzugefügt. wenn Sie jedoch in der Mitte der Schritte einen Abfrageschritt einfügen, kann in nachfolgenden Schritten ein Fehler auftreten. Power Query zeigt eine Warnung Schritt einfügen an, wenn Sie versuchen, einen neuen Schritt einfügen und der neue Schritt Felder, z. B. Spaltennamen, ändert, die in einem der Schritte verwendet werden, die auf den eingefügten Schritt folgen.

  1. Wählen Sie im Einstellungen Abfrageabfrage unter Angewendete Schritte denSchritt aus, der dem neuen Schritt und der zugehörigen Formel unmittelbar vorausgehen soll.

  2. Wählen Sie links neben der Symbol 'Funktion' das Symbol Schritt hinzufügen aus. Alternativ können Sie mit der rechten Maustaste auf einen Schritt klicken und dann Schritt danach einfügen auswählen.Eine neue Formel wird im Format := <nameOfTheStepToReference>

    erstellt, z. B. =Production.WorkOrder.

  3. Geben Sie die neue Formel mithilfe des Formats ein:

    =Class.Function(ReferenceStep[,otherparameters])

    Angenommen, Sie haben eine Tabelle mit der Spalte "Gender", und Sie möchten eine Spalte mit dem Wert "Ms" hinzufügen. oder "Herr", je nach Geschlecht der Person. Die Formel würde wie=Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Beispielformel

Neuanordnen eines Schritts

  • Klicken Sie im Einstellungen Unter Angewendete Schritte mitder rechten Maustaste auf den Schritt, und wählen Sie dann Nach oben oder Nach unten aus.

Schritt löschen

  • Wählen Sie links Schritt löschen Schritt das Symbol Löschen aus, oder klicken Sie mit der rechten Maustaste auf den Schritt, und wählen Sie dann Löschen oder Bis Ende löschen aus. Das Symbol Schritt löschen Löschen steht auch links neben der Formelleiste zur Verfügung.

In diesem Beispiel wandeln wir den Text in einer Spalte mithilfe einer Kombination von Formeln im Erweiterten Editor in die entsprechende Hülle um. 

Sie haben z. B. eine Excel namens "Orders" mit einer Spalte "ProductName", die Sie in die entsprechende Hülle umwandeln möchten. 

Vorher:

Vor

Nach:

Schritt 4 – Ergebnis

Wenn Sie eine erweiterte Abfrage erstellen, erstellen Sie eine Reihe von Abfrageformelschritten basierend auf dem Let-Ausdruck.  Verwenden Sie den Ausdruck let, um Namen zuzuordnen und Werte zu berechnen, auf die dann in der in-Klausel verwiesen wird, die den Schritt definiert. In diesem Beispiel wird dasselbe Ergebnis wie im Abschnitt "Erstellen einer Formel in der Formelleiste" zurückgegeben.

let  
    Source = Text.Proper("hello world")
in  
    Source  

Sie werden sehen, dass jeder Schritt auf einem vorherigen Schritt basiert, indem Sie sich auf einen Schritt nach Name beziehen. Zur Erinnerung: In der Power Query-Formelsprache wird die Zwischen- und Kleinschreibung beachtet.

Phase 1: Öffnen des Erweiterten Editors

  1. Wählen Excel unter Daten> Daten > Datenquellen > Leere Abfrage aus. Weitere Informationen finden Sie unter Erstellen, Laden oder Bearbeiten einer Abfrage in Excel.

  2. Wählen Sie im Power Query-Editor start> erweiterten Editor aus, der mit einer Vorlage des Let-Ausdrucks geöffnet wird.

Erweiterter Editor2

Phase 2: Definieren der Datenquelle

  1. Erstellen Sie den Ausdruck Let mithilfe der Excel. CurrentWorkbook-Funktion wie folgt:

    let#x1

    in
        Source

    Schritt 1 – Erweiterter Editor

  2. Um die Abfrage in ein Arbeitsblatt zu laden, wählen Sie Fertig unddann Start> schließen & laden > schließen & aus.

Ergebnis:

Schritt 1 – Ergebnis

Phase 3: Bewerben der ersten Zeile zu Überschriften

  1. Zum Öffnen der Abfrage wählen Sie auf dem Arbeitsblatt 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 (Power Query).

  2. Wählen Sie im Power Query-Editor die Option Start > Erweiterter Editor aus, die mit der Anweisung geöffnet wird, die Sie in Phase 2: Definierender Datenquelle erstellt haben.

  3. Fügen Sie im Let-Ausdruck #"First Row as Header" und Table.PromoteHeaders function wie folgt hinzu:

    let
        
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3

        #"First Row as Header"

  4. Um die Abfrage in ein Arbeitsblatt zu laden, wählen Sie Fertig unddann Start> schließen & laden > schließen & aus.

Ergebnis:

Schritt 3 – Ergebnis

Phase 4: Ändern der einzelnen Werte in einer Spalte in die entsprechende Hülle

  1. Zum Öffnen der Abfrage wählen Sie auf dem Arbeitsblatt 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 im Power Query-Editor die Option Start> Erweiterter Editor aus, die mit der Anweisung geöffnet wird, die Sie in Phase 3: Hochstieg der ersten Zeile auf Überschriften erstellt haben.

  3. Konvertieren Sie im Let-Ausdruck jeden ProductName-Spaltenwert mithilfe der Funktion Table.TransformColumns in den richtigen Text, verweisen Sie auf den vorherigen Abfrageformelschritt "First Row as Header", fügen der Datenquelle #"Capitalized Each Word" hinzu, und weisen Sie dann dem Ergebnis #"Capitalized Each Word" zu.

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
    in
        #"Capitalized Each Word"

  4. Um die Abfrage in ein Arbeitsblatt zu laden, wählen Sie Fertig unddann Start> schließen & laden > schließen & aus.

Ergebnis:

Schritt 4 – Ergebnis

Sie können das Verhalten der Formelleiste im Power Query-Editor für alle Arbeitsmappen steuern.

Anzeigen oder Ausblenden der Formelleiste

  1. Wählen Sie Datei> Optionen aus, und Einstellungen >Abfrageoptionen aus.

  2. Wählen Sie im linken Bereich unter GLOBALdie Option Power Query-Editor aus.

  3. Aktivieren oder löschen Sie im rechten Bereich unter Layoutdie Option Die Formelleiste anzeigen.

Aktivieren oder Deaktivieren von M IntelliSense

  1. Wählen Sie Datei> Optionen aus, und Einstellungen >Abfrageoptionen aus.

  2. Wählen Sie im linken Bereich unter GLOBALdie Option Power Query-Editor aus.

  3. Aktivieren oder löschen Sie im rechten Bereich unter Formel in der Formelleiste, dem erweiterten Editor und dem Dialogfeld für benutzerdefinierte Spalten die Option M IntelliSense aktivieren.

Hinweis   Das Ändern dieser Einstellung wird beim nächsten Öffnen des Power Query-Editor-Fensters wirksam.

Siehe auch

Hilfe zu Power Query für Excel

Erstellen und Aufrufen einer benutzerdefinierten Funktion

Verwenden der Liste Angewendete Schritte (docs.com)

Verwenden von benutzerdefinierten Funktionen (docs.com)

Power Query M-Formeln (docs.com)

Umgang mit Fehlern (docs.com)

Benötigen Sie weitere Hilfe?

Ihre Office-Fähigkeiten erweitern
Schulungen erkunden
Neue Funktionen als Erster erhalten
Microsoft Insider beitreten

War diese Information hilfreich?

Wie zufrieden sind Sie mit der Übersetzungsqualität?
Was hat Ihre Erfahrung beeinflusst?

Vielen Dank für Ihr Feedback!

×