Power Query Editor einfach erklärt | Power BI Fabric Begriffe

Definition und Einführung

Der Power Query Editor und die M-Programmiersprache sind ein Produkt innerhalb von Microsoft Power BI Desktop bzw. Fabric und dienen dazu, Datenquellen anzuschließen, zu bereinigen und in Tabellen zu transformieren damit diese anschließend für die Datenmodellierung eingesetzt werden können. Das Power BI Modul stammt ursprünglich aus Excel Power Pivot und besitzt umfassende Funktionen wie zum Beispiel den "Power Query Editor" welcher mit der hauseigenen M-Scriptsprache von Microsoft zur Abfrage und Anbindung externer Datenquellen wie Datenbanken und sonstigen Quelldateien eingesetzt werden kann.

Das Produkt wurde ursprünglich als Excel-Add-in entwickelt und ist als solches unter dem Namen Power Query in Power BI Desktop integriert worden. Deshalb öffnet sich Power Query auch in einem neuem Fenster, wenn man diesen in Power BI Desktop aufruft. Darüber hinaus findet sich eine Form des Abfrageeditors in Power BI Dataflows wieder. Dies ist eine Online Variante des besagten Tools und kann im Power BI Service genutzt werden.

Funktionen und Vorteile

Power Query hält selbst keine Daten, sondern ist vielmehr eine Software-Engine zur Verarbeitung von Transformationen, die Daten von der Quelle bis zum Ziel verarbeiten. Die Verarbeitungsschritte innerhalb einer Tabelle werden als sogenannte "Abfrageschritte" dem Entwickler im erweiterten Editor dargestellt. Darüber hinaus hat der Nutzer die Möglichkeit, die interne Abfragesprache , die M-Scripting Sprache zu verwenden, um komplexe Abfrageschritte im erweiterten Editor als Codeblock zu beschreiben. Die Skripte können gespeichert und wiederverwendet werden, sodass Arbeitsprozesse standardisiert und effizient gestaltet werden können.

Anwendungsfälle

Der Power Query wird vor allem für ETL (Extract Transform Load) eingesetzt, um Semantikmodelle oder auch sogenannte Datasets in Power BI mit Daten zu speisen, damit diese in Berichten angezeigt werden können. Entwickler nutzen das Tool auch für die Datenbereinigung und Vorbereitung von Tabellen, da hier alle wesentlichen Features wie Filtern, Unpivotisieren, Gruppieren usw. enthalten sind.

Beladen von Daten über externe Konnektoren

Daten können aus verschiedenen Quellen (Datenbanken, Excel Dateien etc.) in einem Dataset verknüpft werden
Mit Parametern kann man Verbindungen zu Datenquellen dynamisch steuern und somit z.B. flexibel auf unterschiedliche Umgebungen umschalten
Unterstützt wird die geplante und automatisierte Aktualisierungen über Datenquellen im Power BI Service

Bereinigen von importierten Datentabellen

Zur besseren Lesbarkeit können Spalten umbenannt werden. Da die Spalten als Schlüssel für Beziehungen verwendet werden, sollte man hier sorgfältig arbeiten und sich auf ein einheitliches Schema einigen, z.B. CamelCase oder eine andere einheitliche Benennung.
Leere Zeilen filtern
Formatanpassungen von Spalten
Transformation in einheitliche Werte wie Groß-/Kleinschreibung oder Zahlenformate
Entfernen doppelter Zeilen

Zusammenführung und Tabellentransformationen

Zusammenführen (mergen) und Anhängen (append) von Tabellen
Die Unpivot-Funktion verwandelt breite Tabellen in eine längere, schmalere Form und verbessert so die Möglichkeiten zur Analyse
Datenaggregationen wie Summe, Durchschnitt oder Zählen können direkt in Power Query durchgeführt werden
Erstellen benutzerdefinierter Spalten

Vorbereitung des Datenmodells

Ableitung von Dimensionstabellen
Erstellung von Faktentabellen mit Primary Keys für das Sternschema, damit Daten effizient modelliert und Beziehungen klar strukturiert sind
Durch das Entfernen unnötiger Spalten und Zeilen wird das Modell effizient und performant für Abfragen in Power BI optimiert.

Power Query FAQs

Ist Power Query ein eigenständiges Tool?

Power Query ist keine eigenständige Software, sondern in Power BI und Excel integriert , sodass Nutzer Daten direkt aus diesen Programmen verarbeiten können.

Was sind die Hauptvorteile von Power Query für Unternehmen?

Durch automatisierte Transformationen und die Möglichkeit, große Datenmengen effizient zu bereinigen, hilft Power Query Unternehmen, Zeit zu sparen und Datenfehler zu minimieren.

Braucht man für M-Scripting Programmierkenntnisse?

Power Query kann ohne Programmierkenntnisse genutzt werden, bietet jedoch eine M-Sprache für fortgeschrittene Benutzer, die komplexe Transformationen durchführen möchten.

Welche Art von Datenquellen kann ich mit Datenkonnektoren verbinden?

Power Query unterstützt zahlreiche Datenquellen, wie Datenbanken, Excel, CSV-Dateien, Web-Daten und APIs , und bietet damit Flexibilität für die verschiedensten Datenanforderungen.

Power Query M-Scripting

Zeitdimension über eine leere Abfrage in Power BI erstellen

Um eine parametrisierte Zeitdimension im Power Query Editor von Power BI anzulegen können sie folgende Schritte unternehmen. Diese Zeitdimension wird für das Datenmodell in Power BI notwendig sein um die Faktentabellen über Time-Slicers oder Datumsauswahlsfilter dynamisch im Bericht einzuschränken. Die Daten der Zeitdimension können durchaus auch in die Zukunft reichen um Prognosen und Soll-Ist Abgleiche im Projektmanagement oder in der Budgetplanung im Unternehmens zu erlauben.

Schritt-1: Navigieren sie zum Abfrageeditor "Transform data". Danach öffnet sich in einem weiteren Fenster die Ansicht des Power Query Editors.

Schritt-2: Anschließend legen sie zwei Parameter für das Start - und Enddatum fest. Damit steuern sie wann die Zeitdimension starten und enden soll. Ein Datum in die Zukunft kann frei gewählt werden. Beachten sie beim Anlegen des Parameters den korrekten Datentypen für "Datum" einzustellen.

Zuerst müssen die Paramter zur Steuerung des Starts- und Enddatums angelegt werden.

param_date_start

#date(2019, 1, 1) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]

param_date_end

#date(2023, 12, 31) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]

Anschließend kannst du folgenden M-Code als neue Tabelle in Power Query anlegen:

Schritt-3: Legen Sie nun eine " Leere Abfrage " (Blank Query) wie im Screenshot angezeigt an. Die leere Abfrage öffnet sich in einem neuem Fenster. In diesem können sie den Power Query M-Code Scriptsprache wie in in einem Texteditor eingeben und editieren.

Wenn sie eine Tabelle im Power Query Editor "links im Bild" markieren, können sie über den "erweiterten Editor" zurück zu dieser Codebasis navigieren um diese zu einem späteren Zeitpunkt zu modifizieren.

Fügen Sie jetzt den folgenden M-Code in dem Power Query Abfragefenster ein. Achten Sie darauf das die vorherigen Parameter exakt wie im Screenshot benannt sind: param_date_start und param_date_end.

let
    //Variabeln für Date Table
    StartDate = param_date_start,
    EndDate = param_date_end,
    Duration = Duration.Days(Duration.From(EndDate-StartDate))+1,
    Date = List.Dates(StartDate, Duration, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Date, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "year", each Date.Year([date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "month", each Date.Month([date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Month", "date_startofmonth", each #date([year],[month],1)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"date_startofmonth", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"month"}),
    #"Benutzerdefinierte Spalte hinzugefügt" = Table.AddColumn(#"Removed Columns", "Benutzerdefiniert", each Text.Combine({Date.ToText([date], "yyyy"), Date.ToText([date], "MM"), Date.ToText([date], "dd")}), type text),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Benutzerdefinierte Spalte hinzugefügt",{{"Benutzerdefiniert", "date_id"}}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"date_id", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Geänderter Typ",{{"date", "Date"}, {"year", "Jahr"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Monat", each Date.Month([Date])),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Added Custom1",{{"Monat", Int64.Type}}),
    tableout =  Table.TransformColumnNames( #"Geänderter Typ1", Text.Lower)
in
 tableout

Im M-Abfrageeditor wird jede Einzeloperation an den Daten wie ein Prozess von oben nach unten geschrieben und ausgeführt. Jede Einzeloperation wird durch ein Komma am Ende der Beschreibungszeile geschlossen. Jede Zeile bekommt einen eindeutigen Objektnamen auf den jeder Schritt auch durch Sprünge wieder zugreifen kann. Für Profis die SQL gut kennen könnten man sagen das jede Einzeloperation einer CTE ähnelt auf die man innerhalb einer SQL-Statements abfragen kann. Mit einem einleitenden "#" kann die Codezeile auskommentiert werden.

Schritt-4: Nach dem Speichern sollten sie nun folgendes Ergebnis bekommen. Jetzt können sie Ihre Änderungen in der Datenbasis vom Power BI Projekt speichern und die Zeitdimensionen mit den Faktentabellen verknüpfen um ein performantes Dataset aufzubauen.

Beachten Sie das die Zeitreihe nach Tagen vollständig sein muss damit bestimme Funktionen in DAX auch korrekt funktionieren.