Erstellen von Power Query-Formeln in Excel

Die Power Query-Editor bietet eine Datenabfrage- und -strukturierungsumgebung für Excel, mit der Sie Daten aus vielen Datenquellen neu strukturieren können. Um das Power Query-Editor-Fenster anzuzeigen, importieren Sie Daten aus externen Datenquellenin ein Excel-Arbeitsblatt, wählen Sie eine Zelle in den Daten aus, und wählen Sie dann Abfrage > Bearbeiten aus. Es folgt eine Zusammenfassung der Standard Komponenten.

1. Das Power Query-Editor Menüband, das Sie zum Strukturieren Ihrer Daten verwenden

2. Der Bereich "Abfragen", den Sie zum Suchen von Datenquellen und Tabellen verwenden

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

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

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

Im Hintergrund basiert jeder Schritt in einer Abfrage auf einer Formel, die in der Bearbeitungsleiste sichtbar ist.

Es kann vorkommen, 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 zu Syntax, Argumenten, Hinweisen, Funktionen und Beispielen finden Sie unter Power Query M-Formelsprache.

Verwenden Sie anhand einer Liste von Fußballmeisterschaften als Beispiel Power Query rohe Daten, die Sie auf einer Website gefunden haben, in eine gut formatierte Tabelle zu verwandeln. Sehen Sie sich an, wie Abfrageschritte und entsprechende Formeln für jede Aufgabe im Bereich Abfrageeinstellungen unter Angewendete Schritte und in der Bearbeitungsleiste erstellt werden.

Vorgehensweise

1. Wählen Sie zum Importieren der Daten Daten > Aus dem Web aus, geben Sie "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" in das Url-Feld ein, und wählen Sie dann OK aus.

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

3. Um den Standardabfragenamen zu ändern, löschen Sie im Bereich Abfrageeinstellungen unter Eigenschaften "Ergebnisse [Bearbeiten]", und geben Sie dann "UEFA-Champs" ein.

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

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

6. Um Zeilen mit dem Wort "Year" zu entfernen, aktivieren Sie den Filterpfeil in Spalte 1, deaktivieren Sie das Kontrollkästchen neben "Year", und wählen Sie dann OK aus.

7. Um die Spaltenüberschriften umzubenennen, doppelklicken Sie darauf, und ändern Sie dann "Spalte1" in "Year", "Column4" in "Winner" und "Column5" in "Final Score".

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

Result

Die folgende Tabelle enthält eine Zusammenfassung der einzelnen angewendeten Schritte und die entsprechende 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 Power Query automatisch tut)	= 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 entfernte Spalten Entfernen anderer Spalten, um nur relevante Spalten anzuzeigen	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Ersetzter Wert Ersetzen von Werten, um Werte in einer ausgewählten Spalte zu sauber	= 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 darauf, die Schritte Quelle, Navigation  und Geänderter Typ zu bearbeiten, da sie von Power Query erstellt werden, um die Datenquelle zu definieren und einzurichten.

Ein- oder Ausblenden der Bearbeitungsleiste

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

• Wählen Sie Ansicht > Layout > Bearbeitungsleiste aus.

Edit a formula in the formula bar

1. Suchen Sie zum Öffnen einer Abfrage, 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.

2. Wählen Sie im Bereich Abfrageeinstellungen unter Angewendete Schritte den Schritt aus, den Sie bearbeiten möchten.

3. Suchen Und ändern Sie in der Bearbeitungsleiste die Parameterwerte, und wählen Sie dann das Symbol eingeben aus, oder drücken Sie die EINGABETASTE. Ändern Sie diese Formel beispielsweise so, dass auch Column2:
   
   Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Wählen Sie das Symbol eingeben aus, oder drücken Sie die EINGABETASTE, um die neuen Ergebnisse anzuzeigen, die in der Datenvorschau angezeigt werden.

5. Um das Ergebnis in einem Excel-Arbeitsblatt anzuzeigen, wählen Sie Start > Schließen & Laden aus.

Erstellen einer Formel in der Bearbeitungsleiste

Für ein einfaches Formelbeispiel konvertieren wir einen Textwert mithilfe der Text.Proper-Funktionin die richtige Groß-/Kleinschreibung.

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

2. Geben Sie in der Bearbeitungsleiste=Text.Proper("text value")ein, und wählen Sie dann das Symbol eingeben aus, oder drücken Sie die EINGABETASTE.
   
   Die Ergebnisse werden in der Datenvorschau angezeigt.

3. Um das Ergebnis in einem Excel-Arbeitsblatt anzuzeigen, wählen Sie Start > Schließen & Laden aus.

Ergebnis:

 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 eine Abfrage möglicherweise unterbrochen werden.  Überprüfen Sie die Ergebnisse immer in der Datenvorschau.

Wichtig    Achten Sie darauf, die Schritte Quelle, Navigation  und Geänderter Typ zu bearbeiten, da sie von Power Query erstellt werden, um die Datenquelle zu definieren und einzurichten.

Bearbeiten einer Formel mithilfe eines Dialogfelds

Diese Methode verwendet Dialogfelder, die je nach Schritt variieren. Sie müssen die Syntax der Formel nicht kennen.

1. Suchen Sie zum Öffnen einer Abfrage, 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.

2. Wählen Sie im Bereich Abfrageeinstellungen unter Angewendete Schritte das 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 bearbeiten aus.

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

Einfügen eines Schritts

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

1. Wählen Sie im Bereich Abfrageeinstellungen unter Angewendete Schritte den Schritt aus, den Sie unmittelbar vor dem neuen Schritt und der entsprechenden Formel ausführen möchten.

2. Wählen Sie links neben der Bearbeitungsleiste das Symbol Schritt hinzufügen aus. Alternativ können Sie mit der rechten Maustaste auf einen Schritt klicken und dann Schritt nach einfügen auswählen. Eine neue Formel wird im Format erstellt:
   
   = <nameOfTheStepToReference>, z. B. =Production.WorkOrder.

3. Geben Sie die neue Formel im folgenden Format ein:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Angenommen, Sie verfügen über eine Tabelle mit der Spalte Geschlecht und möchten eine Spalte mit dem Wert "Ms" hinzufügen. oder "Mr.", je nach Geschlecht der Person. Die Formel wäre:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Neuanordnen eines Schritts

• Klicken Sie im Bereich Abfrageeinstellungen unter Angewendete Schritte mit der rechten Maustaste auf den Schritt, und wählen Sie dann Nach oben oder Nach unten aus.

Schritt löschen

• Wählen Sie links neben dem 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 zum Ende löschen aus. Das Symbol löschen ist auch links neben der Bearbeitungsleiste verfügbar.

In diesem Beispiel konvertieren wir den Text in einer Spalte mithilfe einer Kombination von Formeln im Erweiterter Editor in die richtige Groß-/Kleinschreibung. 

Beispielsweise verfügen Sie über eine Excel-Tabelle namens Orders mit einer ProductName-Spalte, die Sie in die richtige Groß-/Kleinschreibung konvertieren möchten. 

Bevor:

Nach:

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

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

Sie sehen, dass jeder Schritt auf einem vorherigen Schritt aufbaut, indem sie auf einen Schritt anhand des Namens verweisen. Zur Erinnerung: Bei der Power Query Formula Language wird die Groß-/Kleinschreibung beachtet.

Phase 1: Öffnen des Erweiterter Editor

1. Wählen Sie in Excel Daten > Daten abrufen > Andere Quellen > 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 > Erweiterter Editor aus, das mit einer Vorlage des let-Ausdrucks geöffnet wird.

Phase 2: Definieren der Datenquelle

1. Erstellen Sie den let-Ausdruck mithilfe der Excel.CurrentWorkbook-Funktion wie folgt:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Um die Abfrage in ein Arbeitsblatt zu laden, wählen Sie Fertig und dann Start > Schließen & Laden > Schließen & Laden aus.

Ergebnis:

Phase 3: Höherstufen der ersten Zeile in Kopfzeilen

1. Um die Abfrage zu öffnen, wählen Sie im 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 Start > Erweiterter Editor aus, die mit der Anweisung geöffnet wird, die Sie in Phase 2: Definieren der Datenquelle erstellt haben.

3. Fügen Sie im let-Ausdruck #"Erste Zeile als Kopfzeile" und Die Funktion Table.PromoteHeaders 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 und dann Start > Schließen & Laden > Schließen & Laden aus.

Ergebnis:

Phase 4: Ändern der einzelnen Werte in einer Spalte in die richtige Groß-/Kleinschreibung

1. Um die Abfrage zu öffnen, wählen Sie im 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 Start > Erweiterter Editor aus, die mit der Anweisung geöffnet wird, die Sie in Phase 3: Höherstufen der ersten Zeile zu Kopfzeilen erstellt haben.

3. Konvertieren Sie im let-Ausdruck jeden ProductName-Spaltenwert mithilfe der Table.TransformColumns-Funktion in richtigen Text, indem Sie auf den vorherigen Abfrageschritt "Erste Zeile als Kopfzeile" verweisen, der Datenquelle #"Capitalized Each Word" hinzufügen und dann #"Capitalized Each Word" dem -Ergebnis zuweisen.
   
   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 und dann Start > Schließen & Laden > Schließen & Laden aus.

Ergebnis:

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

Anzeigen oder Ausblenden der Bearbeitungsleiste

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

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

3. Aktivieren oder deaktivieren Sie im rechten Bereich unter Layout die Option Bearbeitungsleiste anzeigen.

Aktivieren oder Deaktivieren von M IntelliSense

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

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

3. Aktivieren oder deaktivieren Sie im rechten Bereich unter Formel die Option M IntelliSense aktivieren in der Bearbeitungsleiste, im erweiterten Editor und im Dialogfeld für benutzerdefinierte Spalten.

Hinweis   Das Ändern dieser Einstellung wird wirksam, wenn Sie das Power Query-Editor Fenster das nächste Mal öffnen.