Top 5 wiederverwendbare M-Code-Funktionen für Power BI/Excel (Power Query)

Power BIs Power Query (M-Sprache) ermöglicht die Erstellung benutzerdefinierter Funktionen und wiederverwendbarer Abfrageschritte zur Vereinfachung gängiger Aufgaben. Nachfolgend finden Sie fünf vielseitige M-Code-Lösungen – eine Mischung aus Datentransformationshilfen und Analysedienstprogrammen –, die Sie mit minimalen Anpassungen in Ihre Power BI-Abfragen kopieren können. Jede Funktion enthält den vollständigen M-Code, eine Erklärung ihres Zwecks und ein Beispiel für einen Anwendungsfall in realen Projekten.

Die Grafik zeigt die Top 5 M-Code-Funktionen in Power Query, illustriert mit Symbolen für Papierkorb und Warnung, Filter und Nummer 1.

1. TRIM-Funktion im Excel-Stil (Zusätzliche Leerzeichen entfernen)

Zweck: Diese benutzerdefinierte Funktion entfernt überschüssige Leerzeichen (oder ein beliebiges angegebenes Zeichen) aus einer Textzeichenfolge. Sie teilt den Text nach dem Zielzeichen, filtert leere Segmente heraus und fügt sie dann wieder zusammen, wodurch mehrere Leerzeichen effektiv zu einem zusammengefasst werden. Dies ahmt die TRIM-Funktion von Excel nach, die führende/nachgestellte Leerzeichen entfernt und aufeinanderfolgende interne Leerzeichen auf ein einziges Leerzeichen reduziert. (Im Gegensatz dazu entfernt die in Power Query integrierte Funktion Text.Trim nur führende/nachgestellte Leerzeichen, und Text.Clean entfernt nicht druckbare/Steuerzeichen, berücksichtigt jedoch keine zusätzlichen internen Leerzeichen.)

Anwendungsbeispiel aus der Praxis: Verwenden Sie ExcelTrim, um unübersichtliche Textdaten wie Namen oder Adressen zu bereinigen. Beispielsweise wird „Acme Corp“ in „Acme Corp“ umgewandelt, wodurch Ihre Textfelder für die Analyse standardisiert werden (z. B. um einheitliche Firmennamen in einer Dimensionstabelle sicherzustellen).

2. Dynamische Unpivot-Transformation

Zweck: Diese Funktion entpivotiert eine Tabelle dynamisch und wandelt alle Nicht-Schlüsselspalten in Attributwertzeilen um. In Power Query generiert das Unpivot-Tool der Benutzeroberfläche Code mit einer fest codierten Liste der zu entpivotierenden Spaltennamen [4] . Dieser statische Ansatz funktioniert nicht mehr, wenn neue Spalten erscheinen (z. B. ein neues Jahr in Ihren Daten) – diese bleiben bei der Aktualisierung entpivotiert. DynamicUnpivot vermeidet dies, indem es die Spaltennamen zur Laufzeit abruft und die angegebenen Schlüsselspalten aus der Liste entfernt. Die verbleibenden Spalten werden dann entpivotiert, sodass sich Ihre Abfrage automatisch an Schemaänderungen anpasst.

Anwendungsfall aus der Praxis: Angenommen, Sie importieren eine Umsatzprognosetabelle mit Spalten für jeden Monat („Jan“, „Feb“ usw.). Mit DynamicUnpivot können Sie Monatsspalten in Zeilen umwandeln (z. B. eine Spalte „Month“ und eine Spalte „SalesValue“), ohne jeden Monat einzeln auflisten zu müssen. Wenn die Daten des nächsten Jahres eine Spalte „Jan-NextYear“ hinzufügen, wird diese automatisch entpivotiert. So wird sichergestellt, dass beim Aktualisieren des Berichts keine Daten verloren gehen.

3. Hinzufügen einer bedingten Flag-Spalte (z. B. Flag für fehlende Daten)

Zweck: Diese Transformation erstellt eine Flag-Spalte (binärer Indikator 0/1) basierend auf einer Bedingung – in diesem Fall, ob eine Zeile fehlende Werte enthält. Sie verwendet Record.FieldValues, um den Zeilendatensatz in eine Werteliste umzuwandeln, und prüft anschließend mit List.AnyTrue, ob ein Wert in dieser Liste null oder ein leerer Text (nach dem Trimmen) ist. Ist ein Feld leer, wird das Flag auf 1 gesetzt, andernfalls auf 0. Sie können die Bedingungslogik anpassen, um andere Szenarien zu kennzeichnen (z. B. Werte über/unter einem Schwellenwert oder benutzerdefinierte Geschäftsregeln).

Anwendungsfall aus der Praxis: Verwenden Sie ein Flag für fehlende Daten, um unvollständige Datensätze schnell zu identifizieren. Fügen Sie beispielsweise in einer Kundendatentabelle HasMissingData = 1 für jeden Kundendatensatz mit einem Null- oder leeren Feld hinzu. Dieses Flag kann Visualisierungen oder Filter in Power BI steuern (hervorheben von Datensätzen, deren Daten bereinigt werden müssen) oder in nachfolgenden Abfrageschritten verwendet werden, um unvollständige Zeilen vor dem Laden der Daten zu entfernen oder zu korrigieren.

4. Identifizieren fehlerhafter Zeilen

Zweck: Dieser Helfer isoliert fehlerhafte Zeilen in Ihrer Abfrage. Die Power Query-Funktion Table.SelectRowsWithErrors gibt nur die Zeilen zurück, die in mindestens einer Zelle einen Fehler aufweisen. Sie können optional eine Liste der zu prüfenden Spalten angeben (falls nicht angegeben, werden alle Spalten geprüft). Indem Sie Ihren Datensatz auf die problematischen Zeilen filtern, können Sie diese Fehler überprüfen, protokollieren oder Korrekturtransformationen anwenden.

Anwendungsfall aus der Praxis: Stellen Sie sich vor, Sie importieren eine CSV-Datei und einige Zeilen enthalten in einer konvertierten Spalte den Fehler „Fehler“ (z. B. ein Textwert in einem numerischen Feld, der einen Konvertierungsfehler verursacht). Mit FindErrorRows können Sie alle fehlerhaften Zeilen zur Überprüfung extrahieren. Sie können sie beispielsweise in eine separate Power BI-Tabelle oder CSV-Datei ausgeben, um Datenprobleme zu untersuchen und die Datenqualität vor der weiteren Analyse sicherzustellen.

5. Hinzufügen einer Rangspalte zu einer Tabelle

Zweck: Diese Funktion hängt basierend auf einem oder mehreren Sortierkriterien eine Rangnummer an jede Zeile einer Tabelle an. Sie nutzt Table.AddRankColumn von Power Query (verfügbar seit dem Update vom Juni 2022), um die Daten zu bewerten. Sie geben eine Liste von Spalten mit Sortierrichtungen an (z. B. { {"Revenue", Order.Descending} }, um nach Umsatz vom höchsten bis zum niedrigsten zu sortieren). Standardmäßig verwendet das obige Beispiel RankKind.Competition, d. h. Gleichstände erhalten denselben Rang und der nächste Rang wird übersprungen (z. B. 1, 1, 3, 4). Andere Rankingmethoden wie Dense (keine Lücken in der Sequenz) sind ebenfalls verfügbar. Wenn Sie mehrere Sortierschlüssel angeben (z. B. {"Region", Order.Ascending}, {"Sales", Order.Descending}), wird das Ranking nach den ersten Schlüsseln partitioniert – in diesem Beispiel ergibt sich effektiv ein Rang innerhalb jeder Region .

Anwendungsfall aus der Praxis: Verwenden Sie Rankings, um Top-N-Berichte oder -Flags zu erstellen . Fügen Sie beispielsweise eine RevenueRank-Spalte hinzu, um Kunden nach Umsatz zu sortieren und so ganz einfach die Top 10 Kunden herauszufiltern. Ebenso können Sie durch Rankings innerhalb von Gruppen (z. B. durch Sortierung der Umsätze nach Produkt innerhalb jeder Kategorie ) die meistverkauften Produkte in jeder Kategorie identifizieren. Diese Vorberechnung in Power Query kann DAX oder die Visualisierungslogik vereinfachen, wenn Sie Spitzenreiter/Nachzügler in Ihren Daten hervorheben müssen.