Einführung in Power Query M Code

Power Query ist ein sehr leistungsfähiges Tool zur Datenextraktion und -transformation, das in Excel 2016 (oder höher), Excel für Office 365 und Power BI integriert ist.

Sie finden es auf der Registerkarte „Daten“ im Abschnitt „Get & Transform Data“ des Menübands.

Es ist sehr leistungsfähig und auch sehr einfach zu bedienen, und der Abfrage-Editor hat eine super intuitive Benutzeroberfläche für einen Excel-Nutzer. Viele Transformationsschritte können ganz einfach über die Multifunktionsleiste des Power Query Editors ausgeführt werden, und Sie müssen keinen Code kennen, um Ihre Daten zu bereinigen und zu formen.

Hinter den Kulissen des benutzerfreundlichen Editors übersetzt Excel jeden Schritt in Ihrem Transformationsprozess aus diesen Multifunktionsleistenbefehlen in die Power Query M-Code-Sprache.

Dieser Beitrag führt Sie in die Grundlagen der Power Query M-Code-Sprache ein und setzt voraus, dass Sie die Grundlagen von Power Query bereits kennen.

Inhaltsverzeichnis

Was ist M-Code?

Das M steht für „Data Mash-up“, denn bei Power Query geht es darum, eine Verbindung zu verschiedenen Datenquellen herzustellen und diese zu „mashen“.

M-Code ist die Sprache hinter den Kulissen von Power Query. Wenn Sie eine Datentransformation in der Power Query-Editoroberfläche erstellen, schreibt Excel den entsprechenden M-Code für die Abfrage.

M ist eine funktionale Sprache, was bedeutet, dass sie hauptsächlich mit Funktionen geschrieben wird, die aufgerufen werden, um Ergebnisse auszuwerten und zurückzugeben. Für M-Code steht eine sehr große Bibliothek mit vordefinierten Funktionen zur Verfügung, und Sie können auch eigene Funktionen erstellen.

Wo können Sie Power Query M-Code schreiben?

Wenn Sie anfangen wollen, M-Code zu schreiben oder zu bearbeiten, müssen Sie wissen, wo Sie dies tun können. Es gibt zwei Stellen, an denen dies möglich ist: in der Formelleiste oder im erweiterten Editor.

Die Formelleiste

Für jeden Schritt, der im Editor UI erstellt wird, können Sie den entsprechenden M-Code in der Formelleiste anzeigen.

Wenn Sie die Formelleiste nicht sehen, gehen Sie zur Registerkarte „Ansicht“ und vergewissern Sie sich, dass die Option „Formelleiste“ aktiviert ist.

Sie können den M-Code für jeden Schritt in einer Abfrage bearbeiten, indem Sie in die Formel klicken und den vorhandenen Code bearbeiten. Wenn Sie fertig sind, können Sie die Änderungen übernehmen, indem Sie entweder auf das Häkchen klicken oder die Eingabetaste drücken. Sie können Ihre Änderungen auch verwerfen, indem Sie auf das X klicken oder Esc drücken.

Sie können auch völlig neue Schritte in Ihrer Abfrage mit der Formelleiste erstellen, indem Sie auf das fx-Symbol neben der Formelleiste klicken. Dadurch wird ein neuer Schritt erstellt, der sich namentlich auf den vorherigen Schritt bezieht, und dann können Sie jeden beliebigen M-Code erstellen.

Der erweiterte Editor

Die Formelleiste zeigt nur den M-Code für den aktuell ausgewählten Schritt in der Abfrage an, aber im erweiterten Editor können Sie den M-Code für die gesamte Abfrage anzeigen und bearbeiten.

Sie können den erweiterten Editor an zwei Stellen im Editor-Ribbon öffnen. Drücken Sie entweder auf der Registerkarte „Home“ oder auf der Registerkarte „Ansicht“ auf die Schaltfläche „Erweiterter Editor“.

Trotz der Bezeichnung „erweitert“ ist der Editor der einfachste Code-Editor, den Sie sehen werden, und enthält (noch) keine Intellisense-Autovervollständigungs-, Syntaxhervorhebungs- oder Autoformatierungsfunktionen.

Der erweiterte Editor zeigt den Abfragenamen, den M-Code für die Abfrage und eine Warnung zu Syntaxverletzungen im M-Code an. Das war’s!

Standard-Funktionsbibliothek

Da es sich bei M-Code um eine funktionale Sprache handelt, dreht sich alles um die Funktionen, und M-Code wird mit einer großen Bibliothek vordefinierter Funktionen geliefert, die als Standardbibliothek bezeichnet wird.

Informationen zu allen verfügbaren Standardbibliotheksfunktionen finden Sie auf der Microsoft-Webseite Power Query M Reference, einschließlich Funktionssyntax und Beispiele.

Die Standardbibliothek kann auch vom Power Query-Editor aus mit dem Schlüsselwort #shared durchsucht werden.

Nach der Eingabe in die Formelleiste können Sie dann alle verfügbaren Funktionen durchsuchen, indem Sie auf das Wort Funktion rechts neben dem Funktionsnamen klicken. Sie finden dort die gleiche Syntax und die gleichen Beispiele wie auf der Referenz-Webseite.

Groß- und Kleinschreibung

Eines der ersten Dinge, die man beim Schreiben von M-Code beachten muss, ist die Groß- und Kleinschreibung in dieser Sprache.

Das bedeutet, dass x nicht dasselbe ist wie X oder „abc“ nicht dasselbe ist wie „ABC“. Dies gilt für alle Werte, Variablen, Funktionen usw.

Ausdrücke und Werte in Power Query

In Power Query dreht sich alles um Ausdrücke und Werte.

Ein Ausdruck ist etwas, das ausgewertet werden kann, um einen Wert in Power Query zurückzugeben. 1 + 1 ist ein Ausdruck, der den Wert 2 ergibt.

Ein Wert ist eine einzelne Dateneinheit. Werte können Einzelwerte wie Zahlen, Text, logische Werte, Null, binäre Werte, Datum, Zeit, Datetime, Datetimezone oder Dauern sein.

Werte können auch komplexere Strukturen als Einzelwerte haben, wie Listen, Datensätze und Tabellen. Sie können auch Werte haben, die eine Kombination aus Listen, Datensätzen und Tabellen sind. Listen von Listen, Tabellen von Listen, Tabellen von Tabellen usw… sind alles mögliche Wertstrukturen.

Einzelne literalische Werte

Einzelne literalische Werte sind der Grundbaustein für alle anderen Werte.

123,45 ist ein Zahlenwert.
„Hello World!“ ist ein Textwert.
true ist ein logischer Wert.
null stellt das Fehlen eines Wertes dar.

Einzelne intrinsische Werte

Intrinsische Werte werden mit Hilfe der verschiedenen intrinsischen Funktionen konstruiert.

#Zeit(Stunden, Minuten, Sekunden)
#Datum(Jahre, Monate, Tage)
#Datenzeit(Jahre, Monate, Tage, Stunden, Minuten, Sekunden)
#Datenzeitzone( Jahre, Monate, Tage, Stunden, Minuten, Sekunden, Offset-Stunden, Offset-Minuten)
#Dauer(Tage, Stunden, Minuten, Sekunden)

Um zum Beispiel das Datum 2018-12-31 zu konstruieren, müssten Sie es mit der intrinsischen Funktion #date(2018, 12, 31) konstruieren.

Strukturierte Werte

Listen

Eine Liste ist eine geordnete Folge von Werten.

Sie können eine Liste mit geschweiften Klammern definieren. {1, 2, 3} ist eine Liste, die die Zahlen 1, 2 und 3 enthält. Da die Reihenfolge wichtig ist, ist dies nicht die gleiche Liste wie {3, 2, 1}.

{„Hallo“, „Welt“} ist eine Liste, die den Text „Hallo“ und „Welt“ enthält.

Listen von Listen sind auch möglich, so ist {{1, 2}, {3, 4, 5}} eine Liste von zwei Listen. Die erste Liste enthält die Zahlen 1 und 2 und die zweite Liste enthält die Zahlen 3, 4 und 5.

Sie können aufeinanderfolgende Listen mit dem Format {x..y} erstellen. {2..5} wird die Liste {2, 3, 4, 5} erzeugen. Dies funktioniert auch für Textzeichen. {„a“…d“} erzeugt die Liste {„a“, „b“, „c“, „d“}.

Sie können auch eine Liste ohne Elemente haben, {} ist die leere Liste.

Da Listen geordnet sind, können wir Elemente in der Liste mit einer Null-basierten Indexnummer referenzieren. {1, 2, 3}{2} wird zu 3 ausgewertet, da dies das 2. Element in der Liste ist (basierend auf einem Null-Index).

Datensätze

Ein Datensatz ist eine geordnete Folge von Feldern. Jedes Feld besteht aus einem Feldnamen, der das Feld eindeutig identifiziert, und einem Feldwert, der ein beliebiger Wert sein kann.

Sie können einen Datensatz mit eckigen Klammern definieren. ist ein Datensatz mit zwei Feldern. Das erste Feld im Datensatz hat den Feldnamen „Vorname“ und den Wert „John“. Das zweite Feld im Datensatz hat den Feldnamen „Alter“ und den Wert „38“.

Datensätze aus Datensätzen sind auch möglich, ] ist ein Datensatz mit einem Feld mit dem Feldnamen „Person“ und einem Feldwert, der ein Datensatz ist.

Leere Datensätze sind auch möglich, ist der leere Datensatz.

Sie können den Feldwert in einem Datensatz durch seinen Feldnamen referenzieren. wird zu „John“ ausgewertet.

Tabellen

Eine Tabelle ist eine geordnete Folge von Zeilen, wobei jede Zeile eine Liste ist.

Tabellen können nur mit einer intrinsischen Funktion konstruiert werden. Sie können eine Tabelle mit der Funktion #table() aus einer Liste von Spaltenüberschriften und einer Liste von Zeilen konstruieren.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}})Dabei wird eine Tabelle mit 2 Spalten, 3 Zeilen und den Spaltenüberschriften Buchstaben und Zahlen erstellt.

Es ist möglich, eine leere Tabelle mit leeren Listen in der intrinsischen Funktion #table() zu erstellen. #table({}, {}) erzeugt eine leere Tabelle.

Sie können jeden Wert in einer Tabelle mit dem nullbasierten Zeilenindex und dem Namen der Spaltenüberschrift referenzieren. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} wird zu „C“ ausgewertet, da dies die 2. Zeile (basierend auf einem Null-Index) der Spalte Buchstaben ist.

Ausdrücke

Ausdrücke sind alles, was zu einem Wert ausgewertet werden kann. Das gilt auch für Werte selbst. Zum Beispiel ergibt der Ausdruck 1 den Wert 1.

Auch wenn man normalerweise denkt, dass Ausdrücke aus komplexeren Operationen oder Funktionen bestehen.

Zum Beispiel:

Der Ausdruck 1 + 1 ergibt 2.
Der Ausdruck 3 > 2 ergibt wahr.
Der Ausdruck „Hello “ & „World“ wird zu „Hello World“ ausgewertet.
Der Ausdruck Text.Upper(„Hello World“) wird zu „HELLO WORLD“ ausgewertet.

Operatoren

Neben der Standardbibliothek gibt es im M-Code auch eine Liste von speziellen Funktionen, die Operatoren genannt werden. Diese nehmen genau zwei Werte (oder Ausdrücke, die zu einem Wert ausgewertet werden) und geben einen einzigen Wert zurück.

Arithmetik

M-Code verfügt über die grundlegenden arithmetischen Operatoren, die Sie aus dem regulären Excel kennen: +, -, * und /. Mit diesen können Sie Werte addieren, subtrahieren, multiplizieren bzw. dividieren.

Sie können mit verschiedenen anderen Arten von Werten als nur Zahlen verwendet werden. Zum Beispiel können Sie eine Dauer zu einem Datum hinzufügen.

#Datum(2018,12,25) + #Dauer(7, 0, 0, 0) ergibt 2019-01-01.

Vergleich

Sie können Werte in M-Code mit den Vergleichsoperatoren <, >, <=, >=, =, <> vergleichen.

x < y wird als wahr ausgewertet, wenn x kleiner als y ist.
x > y wird als wahr ausgewertet, wenn x größer als y ist.
x <= y wird als wahr ausgewertet, wenn x kleiner als oder gleich y ist.
x >= y wird als wahr ausgewertet, wenn x größer als oder gleich y ist.
x = y wird als wahr ausgewertet, wenn x gleich y ist.
x <> y wird als wahr ausgewertet, wenn x nicht gleich y ist.

Diese können mit verschiedenen Arten von Werten verwendet werden. Zum Beispiel können Sie zwei Listen mit dem Gleichheitsoperator vergleichen.

{1,2,3,4} = {1,2,3} wird als falsch ausgewertet, da die Listen nicht gleich sind.

Verkettung und Zusammenführung

Sie können Text verketten und Listen, Datensätze und Tabellen mit dem Ampersand-Operator & zusammenführen.

Zum Beispiel:

"Hello " & "World" wird als „Hallo Welt“ ausgewertet.

{1,2,3} & {3,4,5} ergibt {1,2,3,3,4,5}.

Logisch

Mit den Operatoren not, and und or können Sie Operationen mit booleschen Werten (oder Ausdrücken, die zu booleschen Werten führen) durchführen.

Code kommentieren

Wie Sie es von jeder Programmiersprache erwarten würden, ist es möglich, Kommentare zu Ihrem Code hinzuzufügen.

Es gibt zwei Arten von Kommentaren in M-Code. Einzeilige Kommentare und mehrzeilige Kommentare.

Einzeilige Kommentare

M code goes hereM code goes here //This is a single line commentM code goes here

Ein einzeiliger Kommentar kann erstellt werden, indem dem Kommentar zwei Schrägstrichzeichen // vorangestellt werden. Alles, was davor in derselben Zeile steht, wird als M-Code interpretiert, alles danach als Kommentar.

Mehrzeilige Kommentare

M code goes here /*This is a commenton multiple lines*/ M code goes here

Ein mehrzeiliger Kommentar kann erstellt werden, indem der Kommentar zwischen die Zeichen /* und */ gesetzt wird. Alles, was außerhalb dieser Zeichen steht, wird als M-Code interpretiert. Alles dazwischen wird als Kommentar interpretiert.

Let-Anweisung

Mit der let-Anweisung kann eine Reihe von Werten ausgewertet und Variablennamen zugewiesen werden, die dann in einem nachfolgenden Ausdruck verwendet werden, der auf die in-Anweisung folgt.

let a = 1, b = 2, c = a + bin c

Dieser Ausdruck besteht aus drei Ausdrücken, die nach der let-Anweisung ausgewertet werden. Jeder Ausdruck wird durch ein Komma getrennt, mit Ausnahme des letzten Ausdrucks vor der in-Anweisung. In diesem Beispiel ergibt der gesamte let- und in-Ausdruck den Wert 3.

let c = a + b, b = 2, a = 1in c

Man könnte meinen, dass die Ausdrücke innerhalb einer let-Anweisung in der Reihenfolge erscheinen müssen, in der sie ausgewertet werden sollen. Das ist aber nicht der Fall! Der obige Code ist vollkommen gültig und wird auch zu 3 ausgewertet. Der M-Code-Auswerter berechnet automatisch die Reihenfolge der erforderlichen Berechnungen auf der Grundlage der Abhängigkeiten der Ausdrücke.

Es ist natürlich einfacher für eine Person, den M-Code zu lesen, wenn er in der Reihenfolge der Auswertung geschrieben ist, aber es gibt noch einen weiteren Vorteil. Die Ausdrücke werden im Fenster „Angewandte Schritte“ als separate Schritte angezeigt. Wenn die Ausdrücke nicht in der richtigen Reihenfolge geschrieben werden, erscheinen sie als ein kombinierter Schritt.

let a = 1, b = 2in a + b

Sie können Ausdrücke auch innerhalb des in-Teils eines let… in… Ausdrucks auswerten.

Variablennamen

let #"Is this a variable name? Wow!!!" = 1 + 1in #"Is this a variable name? Wow!!!"

Sie können Ihren Ausdrücken fast jeden beliebigen Namen zuweisen, indem Sie die Zeichen #““ verwenden. Sie können sogar Leerzeichen und andere Sonderzeichen verwenden. Die einzige Ausnahme ist die Verwendung von reservierten Schlüsselwörtern.

Die Variablennamen sind die Namen, die in den angewandten Schritten des Abfrage-Editors erscheinen, daher ist die Möglichkeit, Leerzeichen zu verwenden, eine großartige Funktion.

Each-Anweisungen

Der Each-Ausdruck ist eine Abkürzung für die Deklaration von Funktionen, die einen einzelnen Parameter namens _ (Unterstrich) annehmen.

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", each 2*)in #"Added Custom"

In diesem Beispiel erstellen wir eine neue Spalte, die die Spalte „Zahlen“ für jede Zeile mit 2 multipliziert.

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", (_) => 2*_)in #"Added Custom"

Wir können dieselbe Abfrage mit der Unterstrich-Syntax erstellen, die semantisch äquivalent zur Each-Anweisung ist. Beide Abfragen funktionieren auf die gleiche Weise.

If Then Else-Anweisungen

M-Code ist im Vergleich zu anderen Sprachen ziemlich spärlich, wenn es um logische Ausdrücke geht. Es gibt keine Select-Case- oder Schleifenanweisungen. Es gibt nur einen if… then… else… Ausdruck.

if then else

Die Syntax ist einfach und entspricht den meisten anderen Programmiersprachen. Die Syntax ist einfach und wie bei den meisten anderen Programmiersprachen. Sie kann in einer Zeile stehen oder zur besseren Lesbarkeit in separaten Zeilen.

Try Otherwise Statements

Fehler können auftreten, wenn man versucht, Operationen durchzuführen, die bestimmte Datentypen erfordern. Zum Beispiel führt der Versuch, eine Zahl mit einem Textwert zu multiplizieren, zu einem Fehler.

let Source = #table({"Number", "Number and Text"}, {{2, 2}, {2, "Hello"}}), #"Added Custom" = Table.AddColumn(Source, "Product", each try * otherwise 0)in #"Added Custom"

Fehler können durch die Verwendung des Ausdrucks try… otherwise… vermieden werden.

Dadurch werden Fehler in Ihren Abfrageergebnissen vermieden und Sie können Fehler durch beliebige Werte oder Ausdrücke ersetzen.

Funktionen

Eine Funktion ist eine Abbildung von einer Menge von Parameterwerten auf einen Wert. Neben den Funktionen in der Standardbibliothek können Sie mit M-Code auch eigene Funktionen erstellen.

let Product = (x,y) => x * y, Result = Product(2,3)in Result

Diese Abfrage definiert eine Funktion, die zwei Zahlen multipliziert. Dann ruft die Abfrage die Funktion mit den Werten 2 und 3 auf und wertet sie aus, was 6 ergibt.

Funktionen mit optionalen Parametern

Es gibt zwei Arten von Funktionsparametern, einen erforderlichen Parameter und einen optionalen Parameter.

Erforderliche Parameter müssen immer angegeben werden, wenn eine Funktion aufgerufen wird.

Optionale Parameter müssen nicht angegeben werden, wenn eine Funktion aufgerufen wird. Fehlt der optionale Parameter, dann wird der Wert, der an die Funktion übergeben wird, Null sein.

let Product = (x, optional y) => if y is null then x else x * y, Result = Product(2)in Result

Diese Funktion hat ein optionales zweites Argument. Dann ruft die Abfrage die Funktion auf und wertet sie aus, wobei nur der erforderliche Parameter mit dem Wert 2 verwendet wird, der zu 2 ausgewertet wird. Beachten Sie, dass die Funktion berücksichtigen muss, dass y null ist, da sonst ein fehlendes optionales Argument dazu führen könnte, dass die Funktion zu einem Fehler führt.

Rekursive Funktionen

Es ist auch möglich, eine Funktion zu schreiben, die sich auf sich selbst bezieht, indem man den @ Scoping-Operator verwendet.

let Fibonacci = (n) => if n = 1 then 1 else if n = 2 then 1 else @Fibonacci(n-1) + @Fibonacci(n-2), Result = Fibonacci(7)in Result

Die Fibonacci-Folge ist ein Beispiel für eine Funktion, die rekursiv definiert ist. Die nächste Zahl in der Folge ist definiert als die Summe der beiden vorherigen Zahlen. Um die n-te Zahl zu erhalten, muss man also die (n-1)-te und die (n-2)-te Zahl kennen.

Diese Funktion findet die n-te Fibonacci-Zahl, indem sie die (n-1)-te und die (n-2)-te Fibonacci-Zahl addiert.

Die Abfrage ergibt 13, da 13 die siebte Fibonacci-Zahl ist.

Hier sind einige weitere nützliche Beispiele, in denen Sie rekursive Funktionen verwenden können, um die TRIM-Funktion von Excel zu replizieren, um überflüssige Leerzeichen zwischen Wörtern zu entfernen oder Werte auf der Grundlage einer Liste zu suchen und zu ersetzen.

Abfragefunktionen

Die obigen Beispiele definierten eine Funktion innerhalb einer Abfrage und riefen dann die Funktion innerhalb der Abfrage auf und werteten sie aus.

Es ist auch möglich, eine Abfrage zu erstellen, die eine Funktion (eine Abfragefunktion) ist und von anderen Abfragen aus aufgerufen und ausgewertet werden kann.

let FunctionResult = (Argument1, Argument2,...) => let /*M code to evaluate in the function goes here*/ in Resultin FunctionResult

Das ist das allgemeine Format, das zum Erstellen einer Abfragefunktion erforderlich ist. Beachten Sie, dass Sie eine let… in… Anweisung innerhalb der let… in… Anweisung der Abfragefunktion benötigen, um mehrere Schritte auszuführen.

Schlussfolgerungen

Power Query ist eine großartige Funktion in Excel, die Ihnen dabei helfen kann, Ihren Datenimport und Ihre Datentransformation zu automatisieren und zu vereinfachen.

Sie können eine Menge tun, indem Sie nur die grafische Zeigen-und-Klicken-Oberfläche verwenden, ohne den M-Code jemals zu berühren. Aber wenn Ihre Anforderungen fortgeschrittener werden, kann es sein, dass Sie den von Ihnen erstellten M-Code bearbeiten oder Ihren eigenen Code von Grund auf neu schreiben müssen.

Jede neue Programmiersprache ist unlesbar, wenn Sie nicht zuerst die Grundlagen kennen. Dieser Leitfaden wird Sie hoffentlich mit dem M-Code vertraut machen, so dass Sie mit der Erstellung fortgeschrittener Abfragen beginnen können.