Power Query: Mit einer M-Funktion die Ergebnisse einer Auswertung gruppieren

Einer meiner Kunden möchte seine Umsätze nach Preiskategorien auswerten. Die Umsatzdaten werden aus einer SQL-Datenbank mittels Power Query abgerufen und aufbereitet. Die Frage lautet nun, wie in Power Query jeder Umsatz einer der fünf Preiskategorien (A bis E) zugeordnet werden kann.

Klingt nach einem ungefähren SVERWEIS in Power Query. Wie das durch Anfügen von Abfragen und anschließendes Sortieren  realisiert werden kann, habe ich am 26.2.2019 im Blogbeitrag  Ergebnisse in einer Auswertung gruppieren: Wie ich einen ungefähren SVERWEIS in Power Query realisiere gezeigt.

Eine Alternative zu diesem Vorgehen ist das Erstellen einer M-Funktion in Power Query. Das bietet zwei Vorteile:

• Eine M-Funktion ist weniger fehleranfällig.
• Sie lässt sich leicht anpassen und damit auch für andere Fälle wiederverwenden.

 

Nachfolgend beschreibe ich, wie eine solche Funktion erstellt und angepasst wird und für welche Zwecke sie sich noch einsetzen lässt.

Die Funktion erstellen, die zu jedem Umsatz die Preiskategorie ermittelt

• Die Funktion soll jede Zeile in der Kategorientabelle mit dem jeweiligen
  Umsatz vergleichen und dabei unpassende Kategorienzeilen wegfiltern.
• Ist die Kategorientabelle lückenlos, bleibt bei einer Filterung nur eine Kategorienzeile übrig. Aus ihr wird dann die passende Kategorie ausgelesen.

Zunächst lese ich – wie im Blogbeitrag vom 26.2.2019 beschrieben – die beiden Tabellen Umsatz und Kategorien in Power Query ein. Das Erstellen und Einbinden der Funktion funktioniert dann wie folgt:

• Im Power Query-Editor erzeuge ich eine leere Abfrage über Start > Neue Quelle > Andere Quellen.
• Die Abfrage benenne ich fnErmittleKategorie.
• Über Start > Erweiterter Editor passe ich den vorgegebenen Code an.
  Ich ersetze:

  let
    Quelle = ""
  in
    Quelle

  durch

  (Verkaufspreis as number) =>
  let
    Quelle = Kategorien,
    Vergleich = Table.SelectRows(Quelle, each [von]<=Verkaufspreis and [bis unter]>Verkaufspreis),
    Ergebnis = if Table.RowCount(Vergleich) <> 1 then null else Vergleich{0}[Kategorie]
  in
    Ergebnis

Die Funktion in eine benutzerdefinierte Spalte einbauen

Die Abfrage Umsatz erweitere ich nun wie folgt um eine Spalte:

• Abfrage Umsatz markieren,
• Register Spalte hinzufügen > Benutzerdefinierte Funktion aufrufen und
• die im folgenden Bild gezeigten Einstellungen vornehmen.
• Der neuen Spalte weise ich den Datentyp Text zu.

 

Auf Basis dieser Abfrage kann jetzt die in der folgenden Abbildung rechts gezeigte Pivot-Tabelle erstellt werden, die pro Kategorie den Umsatz anzeigt.

Fazit

Die selbst erstellte M-Funktion kann ich leicht anpassen und dann für andere Aufgaben nutzen, beispielsweise, um

• Umsatzdaten auf bestimmte Zeitspannen aufzuteilen oder
• für Mengenangaben die jeweilige Rabattstaffel zu ermitteln.