Erstellen von Power Query-Formeln in Excel

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.

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.

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.

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

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 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 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 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:

 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 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 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.")
   
   

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 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 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:

Nach:

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.

Phase 2: Definieren der Datenquelle

1. Erstellen Sie den Ausdruck Let mithilfe der Excel. CurrentWorkbook-Funktion wie folgt:
   
   let#x1
   
   in
       Source
   
   

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

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:

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:

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.