Tipps zur Verwendung des erweiterten Power Query-Editors (2024)

Wenn Sie zum Erstellen Ihrer Transformationen bisher nur die Benutzeroberfläche (Menüband und Menüs) verwendet haben, entgehen Ihnen einige nette Dinge, die Sie mit dem erweiterten Power Query-Editor tun können.

In diesem Beitrag möchte ich Ihnen zeigen, was im erweiterten Editor enthalten ist, wie Sie darin Code schreiben können und Ihnen hoffentlich etwas mehr Sicherheit bei der Verwendung bei Bedarf geben.

Diese Tipps gelten für Power Query sowohl in Excel als auch in Power BI, aber ich werde Excel zur Demonstration verwenden.

Schau das Video

Tipps zur Verwendung des erweiterten Power Query-Editors (1)

Hier sind meine Dummy-Daten, die aus einer Tabelle in einer Excel-Arbeitsmappe in Power Query geladen wurden.

Tipps zur Verwendung des erweiterten Power Query-Editors (2)

Wenn Sie einen Schritt hinzufügen, arbeiten Sie normalerweise mit Spalten und Zeilen in einer Tabelle.

Als würde man eine Spalte hinzufügen

Tipps zur Verwendung des erweiterten Power Query-Editors (3)

Tipps zur Verwendung des erweiterten Power Query-Editors (4)

oder Zeilen herausfiltern.

Tipps zur Verwendung des erweiterten Power Query-Editors (5)

Tipps zur Verwendung des erweiterten Power Query-Editors (6)

Die GUI (Graphical User Interface) kümmert sich um den Code darunter – sie schreibt den M-Code, um das zu tun, was Sie wollen.

Tipps zur Verwendung des erweiterten Power Query-Editors (7)

Sie können jedoch den erweiterten Editor verwenden, um den von der GUI erstellten Code zu ändern oder Ihre eigenen Schritte zu schreiben.

Codestruktur im erweiterten Editor

Der Code für eine typische Abfrage sieht folgendermaßen aus

Tipps zur Verwendung des erweiterten Power Query-Editors (8)

Der Code ist in zwei Abschnitte unterteilt, den Code nach dem Schlüsselwort let und den Code nach dem Schlüsselwort in.

Die Codezeilen nach let sind Ihre Transformationsschritte. Die Zeile nach in teilt der Abfrage mit, was als Ergebnis der Abfrage zurückgegeben werden soll.

Jeder Schritt hat seine eigene Zeile und jeder Schritt endet mit einem Komma, mit Ausnahme des Schritts vor dem Schlüsselwort in.

Schritte müssen jedoch nicht auf einer eigenen Linie liegen. Wenn ich den Code so bearbeite, dass mehrere Schritte in einer einzelnen Zeile stehen, die einzelnen Schritte jedoch immer noch durch ein Komma getrennt sind

Tipps zur Verwendung des erweiterten Power Query-Editors (9)

Die Abfrage funktioniert immer noch. Es ist jedoch offensichtlich, dass ein einzelner Schritt in jeder Zeile die Lesbarkeit des Codes verbessert.

Konventionen zur Benennung von Schritten

Schrittnamen müssen eindeutig sein.

Von der GUI erstellte Schritte haben normalerweise einen Namen wie #"Schrittname". Obwohl Schrittnamen, die Leerzeichen enthalten, in doppelte Anführungszeichen gesetzt und mit einem # vorangestellt werden müssen, müssen die automatisch generierten Schrittnamen nicht so lauten.

Wenn Sie den Schrittnamen ändern möchten, zum Beispiel den Schritt #"Changed Type", können Sie ihn in Changed_Type ändern und der Schritt funktioniert weiterhin einwandfrei.

Wenn Sie jedoch irgendwo auf den alten Schrittnamen verweisen (normalerweise im nächsten Schritt), müssen Sie auch diesen in den neuen Namen ändern.

Tipps zur Verwendung des erweiterten Power Query-Editors (10)

Quelle ist normalerweise der Name des ersten Schritts, aber Sie können ihn in einen anderen Namen umbenennen. Hier habe ich ihn in „MyData“ umbenannt.

Tipps zur Verwendung des erweiterten Power Query-Editors (11)

Das Abfrageergebnis

Das Ergebnis der Abfrage wird durch das Schlüsselwort in angegeben. Welcher Schritt auch immer hier genannt wird, ist das, was die Abfrage zurückgibt.

Wenn Sie den Namen des Schritts nach dem Schlüsselwort in ändern, gibt die Abfrage das Ergebnis dieses Schritts zurück.

Tipps zur Verwendung des erweiterten Power Query-Editors (12)

Tipps zur Verwendung des erweiterten Power Query-Editors (13)

Manuelles Schreiben von Code

Normalerweise führt jeder Schritt zu einer Tabelle, aber das ist nicht immer der Fall.

Nehmen wir an, ich möchte die Summe der Werte in der Spalte „Betrag“ ermitteln. Zuerst lösche ich die Schritte „Gefilterte Zeilen“ und „Hinzugefügter Index“, sodass diese Tabelle übrig bleibt

Tipps zur Verwendung des erweiterten Power Query-Editors (14)

Ich kann die Spalte „Betrag“ auswählen und auf der Registerkarte „Transformieren“ unter „Statistik“ auf „Summe“ klicken.

Tipps zur Verwendung des erweiterten Power Query-Editors (15)

Dadurch erhalte ich einen einzelnen Wert, die Summe der Werte in der Spalte „Betrag“, und dies ist nun das Ergebnis der Abfrage.

Tipps zur Verwendung des erweiterten Power Query-Editors (16)

Das ist eigentlich nicht das, was ich als Abfrageergebnis haben möchte, aber ich möchte, dass der Gesamtbetrag für spätere Berechnungen verwendet wird.

Wenn ich in den erweiterten Editor gehe, benenne ich zunächst #„Berechnete Summe“ in „Gesamt“ um. Dann verschiebe ich diesen Schritt über den Schritt „Changed_Type“ und ändere den von der Abfrage zurückgegebenen Schritt in „Changed_Type“.

Tipps zur Verwendung des erweiterten Power Query-Editors (17)

und die Abfrage gibt nun wieder eine Tabelle zurück, das Ergebnis des Changed_Type-Schritts.

Der Schritt „Gesamt“ enthält jedoch die Summe der Spalte „Betrag“ und erhält diese Werte aus dem Schritt, der darauf folgt. Es mag seltsam erscheinen, dass diese Schritte „nicht in der richtigen Reihenfolge“ sind, aber so funktioniert Power Query.

Ich kann eine benutzerdefinierte Spalte hinzufügen und den Wert in „Gesamt“ verwenden, um den Prozentsatz zu berechnen, den jeder Wert in „Betrag“ zur Gesamtsumme beiträgt.

Tipps zur Verwendung des erweiterten Power Query-Editors (18)

Wenn man hier die GUI verwendet, sieht der Code so aus

Tipps zur Verwendung des erweiterten Power Query-Editors (19)

Ich erhalte eine neue Spalte, die den Prozentsatz (hier als Dezimalzahl) anzeigt, den der Betrag in dieser Zeile zur Spalte „Gesamtbetrag“ beiträgt.

Tipps zur Verwendung des erweiterten Power Query-Editors (20)

HINWEIS: Ich weiß, dass ich auch eine benutzerdefinierte Spalte hinzufügen und List.Sum für die Spalte „Betrag“ verwenden könnte, aber ich veranschauliche nur, was mit dem erweiterten Editor möglich ist.

Schauen wir uns ein weiteres Beispiel an, in dem ich einen Schritt verwenden kann, um einen Wert für spätere Berechnungen zu speichern.

Erstellen Sie im erweiterten Editor einen neuen Schritt mit dem Namen „Ziel“ und legen Sie ihn auf 500 fest. Damit erstelle ich eine neue Spalte, die anzeigt, welche Beträge über diesem konstanten Wert liegen.

Tipps zur Verwendung des erweiterten Power Query-Editors (21)

Schließen Sie den erweiterten Editor und erstellen Sie dann eine neue benutzerdefinierte Spalte mit diesem Code

Tipps zur Verwendung des erweiterten Power Query-Editors (22)

Dies zeigt, dass 3 Zeilen das Ziel überschritten haben.

Tipps zur Verwendung des erweiterten Power Query-Editors (23)

Wenn ich den Zielwert auf 600 bearbeite

Tipps zur Verwendung des erweiterten Power Query-Editors (24)

Ich muss keinen weiteren Code ändern, aber das Abfrageergebnis ändert sich und zeigt, dass jetzt nur noch eine Zeile das Ziel überschreitet.

Tipps zur Verwendung des erweiterten Power Query-Editors (25)

Hinweis: Sie können den Zielwert auch bearbeiten, indem Sie den Zielschritt in der Liste „Angewandte Schritte“ auswählen und den Wert in der Bearbeitungsleiste ändern

Wie ich gezeigt habe, können Schritte nicht nur zu Tabellen führen, sondern auch zu Werten wie Zahlen und Text und sogar zu Listen und Datensätzen.

Ich zeige ein weiteres Beispiel mit einem Schritt zum Halten einer Liste.

Erstellen Sie im erweiterten Editor einen neuen Schritt mit dem Namen „Favourite_Fruit“ und stellen Sie diesen so ein, dass er einer Liste bestehend aus „Apfel“ und „Orange“ entspricht.

Tipps zur Verwendung des erweiterten Power Query-Editors (26)

Erstellen Sie nach dem Schließen des erweiterten Editors eine neue benutzerdefinierte Spalte und der Code hier verwendet die Funktion List.ContainsAny. Es prüft, ob das Top-Produkt in der Liste in FavouriteFruit enthalten ist, d. h., ob es sich bei dem Top-Produkt um Apfel oder Orange handelt.

Tipps zur Verwendung des erweiterten Power Query-Editors (27)

Tipps zur Verwendung des erweiterten Power Query-Editors (28)

Kommentare

Kommentare können über den erweiterten Editor hinzugefügt werden. Sie können mehrzeilige Kommentare oder einzeilige Kommentare haben.

Mehrzeilige Kommentare sind von /* */ umgeben. Wenn ich also am Anfang des Codes einige Kommentare hinzufügen möchte, kann ich das so machen

Tipps zur Verwendung des erweiterten Power Query-Editors (29)

Einzeilige Kommentare beginnen mit // und können nicht nur dazu verwendet werden, dem Code beim Lesen einige nützliche Informationen hinzuzufügen, sondern werden auch zu Tooltipps für den folgenden Schritt.

Wenn ich über dem Schritt „Gesamt“ einen einzeiligen Kommentar hinzufüge, schließe ich den erweiterten Editor

Tipps zur Verwendung des erweiterten Power Query-Editors (30)

Neben dem Gesamtschritt befindet sich jetzt ein kleines i-Symbol (Information). Dies zeigt an, dass dafür ein Tooltip verfügbar ist. Wenn Sie mit dem Mauszeiger darüber fahren, wird dieser angezeigt.

Tipps zur Verwendung des erweiterten Power Query-Editors (31)

Dieser einzeilige Kommentar wird auch zur Beschreibung für die Schritteigenschaften

Tipps zur Verwendung des erweiterten Power Query-Editors (32)

Tipps zur Verwendung des erweiterten Power Query-Editors (33)

Funktionen

Schauen wir uns zum Schluss das Schreiben einer „Inline“-Funktion an. Sie wird als „inline“ bezeichnet, da es sich nicht um eine separate Abfrage handelt, sondern mit den anderen Codezeilen der Hauptabfrage übereinstimmt.

Ich schreibe eine einfache Funktion namens Double, die jeden empfangenen numerischen Wert verdoppelt.

Oben im Fenster „Erweiterter Editor“ gebe ich den Funktionscode ein. Hier finden Sie eine Erläuterung der Funktionsstruktur.

Tipps zur Verwendung des erweiterten Power Query-Editors (34)

Durch das Schließen des erweiterten Editors wird lediglich der Funktionscode in der Abfrage gespeichert. Um die Funktion verwenden zu können, muss ich sie wie jede andere Funktion aufrufen. Dies kann ich in einer neuen benutzerdefinierten Spalte tun.

Durch Aufrufen der Funktion und Übergeben des Werts aus der aktuellen Zeile der Spalte „Betrag“.

Tipps zur Verwendung des erweiterten Power Query-Editors (35)

Meine Tabelle hat jetzt eine neue Spalte, die die Betragswerte multipliziert mit 2 enthält.

Tipps zur Verwendung des erweiterten Power Query-Editors (36)

Abschluss

Wenn Sie verstehen, was Sie im erweiterten Editor sehen und wie Sie dort Code schreiben und ändern, können Sie einige Dinge tun, die Sie mit der GUI allein nicht tun können.

Ich hoffe, dies hat Ihnen geholfen, dies zu verstehen und Sie sicherer zu machen, es zum Schreiben von M-Code zu verwenden.

Tipps zur Verwendung des erweiterten Power Query-Editors (2024)

FAQs

How do I get better at Power Query? ›

Filter early. It's always recommended to filter your data in the early stages of your query or as early as possible. Some connectors will take advantage of your filters through query folding, as described in Power Query query folding. It's also a best practice to filter out any data that isn't relevant for your case.

How do I remove errors from Power Query editor? ›

To remove rows with errors in Power Query, first select the column that contains errors. On the Home tab, in the Reduce rows group, select Remove rows. From the drop-down menu, select Remove errors.

How do I remove unwanted data from Power Query? ›

Remove rows with errors
  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.
  2. Decide which columns for which you want to remove errors. ...
  3. Select Home > Remove Rows > Remove Errors.

How long does it take to learn Power Query? ›

The basics are easy and might take an hour or so. Getting used to the UI (which is very good) and how Power Query works - it's column based unlike Excel that allows a lot of freedom, might take another hour. After that, years - but that's just like Excel. There's ALWAYS something to learn!

What is the most efficient way to get SQL data in Power Query? ›

By first running the SQL query in a platform like Visual Studio, then saving the file, then uploading to Power Query is the most efficient approach.

What is clean Power Query? ›

Returns a text value with all control characters of text removed.

What is a common error with Power Query? ›

The wrong source location error occurs when a file or database changes location, or a server has crashed, and therefore the source cannot be accessed. Either way, Power Query can't find the source data. After refreshing, an error message like the following will appear, detailing the file location it cannot find.

Can you remove duplicates in Power Query Editor? ›

Power Query allows you to remove duplicates from the data, just like in Excel. And in this tutorial, we will learn to remove duplicate values from the data.

Is there a way to undo Power Query? ›

Undo Last Action

Hitting Delete will allow you to remove that last step. So, Tab key 6 times then Delete is the keyboard shortcut to Undo in Power Query.

How do I undo a redo in Power Query editor? ›

To undo a change, click on the 'Undo' button located on the Power Query Editor ribbon. To redo a change, click on the 'Redo' button, which is next to the 'Undo' button.

How do you replace errors with nulls in Power Query? ›

In Power Query, you can manually replace the errors with null by going to the “Transform” tab then selecting the drop down for “Replace Values” and choosing “Replace Errors”.

How do you delete an applied step in the Power Query Editor? ›

To delete a step, right-click the step and select Delete. Alternatively, select the x next to the step.

How do I remove all duplicates in Power Query? ›

Select a column by clicking the column header. To select more than one column press Shift + Click or Ctrl + Click on each subsequent column. The columns can be contiguous or discontiguous. Select Home > Remove Rows > Remove Duplicates.

What is an option to Unpivot data in the query editor? ›

Unpivot only selected columns

Select the columns you do want to unpivot. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column. Select Transform > Unpivot Only Selected Columns.

Is Power Query easy to learn? ›

Power Query can import and clean millions of rows into the data model for analysis after. The user interface is intuitive and well laid out so it's really easy to pick up. It's an incredibly short learning curve when compared to other Excel tools like formulas or VBA. What is this?

What is the main strength of Power Query? ›

Power Query enables connectivity to a wide range of data sources, including data of all sizes and shapes. Consistency of experience, and parity of query capabilities over all data sources. Highly interactive and intuitive experience for rapidly and iteratively building queries over any data source, of any size.

What is Power Query an excellent tool for? ›

This is the best tool for preparing and transforming the data, Power Query is a component of Microsoft Power BI, Excel, and other data analytics applications in order to extract convert, and load (ETL) data from diverse sources into a structured format appropriate for analysis in reporting.

What big problems does Power Query solve? ›

Power Query can solve all sorts of problems for the Excel user who is wrangling with data. Consolidating, Splitting, Merging, Replacing, Filtering, "UNPIVOTTING!"... the list is comprehensive and seems to be ever growing. Here's just one small and very unusual example.

References

Top Articles
Latest Posts
Article information

Author: Greg O'Connell

Last Updated:

Views: 6280

Rating: 4.1 / 5 (62 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Greg O'Connell

Birthday: 1992-01-10

Address: Suite 517 2436 Jefferey Pass, Shanitaside, UT 27519

Phone: +2614651609714

Job: Education Developer

Hobby: Cooking, Gambling, Pottery, Shooting, Baseball, Singing, Snowboarding

Introduction: My name is Greg O'Connell, I am a delightful, colorful, talented, kind, lively, modern, tender person who loves writing and wants to share my knowledge and understanding with you.