Daten bereinigen und transformieren mit Power Query

Power Query ist die Datenaufbereitungs-Engine von Power BI, eingebaut in Power BI Desktop und Excel. Sie verbinden sich damit mit einer Datenquelle und bereinigen, formen, führen zusammen und transformieren die Daten mit der Sprache M, bevor sie ins Datenmodell Ihres Reports geladen werden. Jeder Klick im Power-Query-Editor wird als eine Zeile M aufgezeichnet, die Sie lesen, bearbeiten oder von Hand schreiben können.

Was Power Query macht

Power Query sitzt zwischen Ihren Quelldaten und Ihrem Report. Sie richten es auf eine Quelle (eine Datenbank, einen Ordner mit CSV-Dateien, eine API, eine Excel-Arbeitsmappe), und es öffnet eine Vorschau dieser Daten in einem eigenen Editor. Jede Transformation, die Sie anwenden, etwa eine Spalte entfernen oder Zeilen filtern, erscheint der Reihe nach als benannter Schritt im Bereich Angewendete Schritte auf der rechten Seite.

Jeder Schritt ist eine Zeile M-Code, und Power Query führt bei jedem Refresh alle erneut aus. Mit Ansicht > Erweiterter Editor sehen Sie den vollständigen let-Ausdruck hinter jeder Abfrage, in dem jeder Schritt in den nächsten einfließt.

Daten bereinigen

Häufige Bereinigungsschritte und die M-Funktionen dahinter:

• Zeilen mit Fehlern entfernen: Table.RemoveRowsWithErrors(Source, {"ColumnName"})

• Doppelte Zeilen entfernen: Table.Distinct(Source)

• Leere Werte herausfiltern: Table.SelectRows(Source, each [ColumnName] <> null)

• Einen Datentyp korrigieren: Table.TransformColumnTypes(Source, {{"OrderDate", type date}})

Wenden Sie diese über das Menüband an (Start > Zeilen entfernen, Start > Duplikate entfernen, den Filterpfeil einer Spaltenüberschrift oder das Typsymbol einer Spaltenüberschrift), schreibt Power Query dieselben Funktionen automatisch. Beide Wege erzeugen identischen M-Code. Soll dieselbe bereinigte Abfrage nach Zeitplan laufen und mehrere Reports speisen, bauen Sie sie besser als Power-BI-Dataflow, statt als Abfrage in einem einzelnen Report.

Daten zusammenführen: ein Beispiel

Angenommen, Sie haben eine Abfrage Products mit ProductID, ProductName, CategoryID und QuantityPerUnit und eine separate Abfrage TotalSales mit ProductID, Year und Total Sales, bereits nach Produkt und Jahr aggregiert. Das Zusammenführen verknüpft beide über die gemeinsame Spalte ProductID:

let
    Source = Products,
    MergedWithSales = Table.NestedJoin(
        Source, {"ProductID"},
        TotalSales, {"ProductID"},
        "TotalSales", JoinKind.LeftOuter
    ),
    ExpandedSales = Table.ExpandTableColumn(
        MergedWithSales, "TotalSales", {"Year", "Total Sales"}, {"Year", "Total Sales"}
    )
in
    ExpandedSales

Table.NestedJoin fügt eine neue Spalte hinzu, die die passenden Zeilen aus TotalSales als verschachtelte Tabelle enthält, eine Zeile pro Treffer. Table.ExpandTableColumn holt dann die gewünschten Spalten aus dieser verschachtelten Tabelle und flacht sie in die Haupttabelle ab. Ein Left Outer Join behält jedes Produkt, mit leeren Werten, wo kein Umsatz passt.

Der Dialog Zusammenführen, erreichbar über Start > Abfragen zusammenführen. Wählen Sie in jeder Tabelle die passende Spalte, wählen Sie eine Join-Art und bestätigen Sie mit OK, um die verschachtelte Spalte zu erzeugen, die der M-Code oben dann erweitert.

Eine berechnete Spalte hinzufügen

Um zwei Spalten Zeile für Zeile zu multiplizieren, nutzen Sie Spalte hinzufügen > Benutzerdefinierte Spalte oder schreiben den Schritt direkt:

Table.AddColumn(#"Changed Type", "Multiply", each [Quantity] * [Price])

each führt den Ausdruck einmal pro Zeile aus und bezieht sich auf die Quantity- und Price-Werte der jeweiligen Zeile. Das Ergebnis ist eine neue Spalte, ohne dass Sie über M hinaus eine weitere Formelsprache lernen müssen.

Ein benutzerdefinierter Spaltenschritt, direkt in der Bearbeitungsleiste geschrieben. Er multipliziert Zeile für Zeile wie der Code oben.

Das Ergebnis automatisieren

Sind die Schritte einer Abfrage richtig, lädt Schließen & übernehmen das Ergebnis in Ihr Datenmodell, und ein geplanter Refresh im Power BI Service führt alle Schritte nach dem von Ihnen gesetzten Zeitplan erneut gegen die Live-Quelle aus. Mit Parametern, die Sie unter Start > Parameter verwalten setzen, tauschen Sie einen Dateipfad, einen Datumsbereich oder einen Servernamen aus, ohne die Abfrage selbst zu bearbeiten. Das ist nützlich, wenn dieselbe Abfrage gegen eine Test- und eine Produktionsquelle laufen muss.

Power Query formt die Daten. Wie Sie die entstehenden Tabellen dann strukturieren, also Faktentabellen, Dimensionstabellen und Beziehungen, ist eine eigene Entscheidung. Siehe unsere Best Practices zur Datenmodellierung in Power BI und, wenn das Modell eine Kalendertabelle braucht, unsere Anleitung zum Bau einer Datumstabelle von Grund auf.

Quellen

• Power Query M function reference - Microsoft Learn

• Merge queries overview - Microsoft Learn