This post is also available in: Englisch

Aufrufe: 10175

Der nachfolgende Artikel ist aufgrund einer Frage anlässlich eines Power BI User Treffens entstanden. Es macht sicherlich mehr Sinn, die Aufgabenstellung mittels DAX zu lösen. Aber trotzdem hat mich die Fragestellung nicht losgelassen und ich zeige Euch, wie ein gruppierter Saldo in Power Query erstellt werden kann.

Mehr über Power BI könnt ihr hier nachlesen.



Willst du Power BI lernen?
Auf unseren neuen Seite Power BI Coach haben wir einen Onlinekurs zusammengestellt.


Einleitung

Für das heutige Beispiel habe ich eine kleine Tabelle erstellt, welche als Ausgangsbasis dient.

Die Tabelle umfasst Lagerbewegungen vom 1.1. bis zum 22.1.2018 für die vier Früchtesorten Apfel, Bananen, Birnen und Orangen. Ziel ist es, einen gruppierten Saldo pro Früchtesorte zu erstellen.

Mit DAX ist diese Aufgabe dank der Zeitintelligenz sehr leicht zu lösen. In Power Query ist die Aufgabe auch machbar, benötigt aber ein paar Extraschritte.

1. Fortlaufenden Saldo bilden

Ich habe die Tabelle in Power Query geladen und die Abfrage in “Lagerbewegungen” umbenannt. Als nächsten Schritt habe ich die Datentypen definiert. Diesen Schritt mache ich immer manuell um sicherzugehen, dass die Datentypen richtig gesetzt sind.

Der nächste Schritt ist die Erstellung einer neuen Abfrage, welche auf die erste Abfrage “Lagerbewegungen” verweist. Die Daten habe ich danach nach Datum sortiert.

Um den fortlaufenden Saldo bilden zu können, benötigen wir eine Indexspalte, beginnend bei 1. Die Indexspalte könnt ihr bequem über die Oberfläche erstellen.

Index erstellen

Danach folgt bereits das Herzstück des Codes, die Bildung des fortlaufenden Saldos.

Gebt dazu folgenden Code in eine neue, benutzerdefinierte Spalte ein:

fortlaufenden Saldo bilden
fortlaufenden Saldo bilden

Die neue Spalte beinhaltet zwei Funktionen.

List.Range

Die innere, also die zweite, Funktion, List.Range, bildet aus der Spalte [Betrag] eine aneinandergehängte Liste.

Diese sieht für die erste Zeile wie folgt aus:

Die zweite Zeile umfasst dann den ersten und den Eintrag der laufenden Zeile:

Und in der 5. Zeile:

Sobald ich die Liste erweitere, wandelt sich meine Abfrage in eine Tabelle um, welche die Einträge wiederholt.

List.Sum

Als letzter Schritt kommt List.Sum ins Spiel. Diese Funktion bildet aus der soeben erstellten Spalte den fortlaufenden Saldo.

Da aber List.Sum eine Liste als Input benötigt, habe ich die beiden Funktionen verkettet und dadurch auch die Erweiterung der Spalten vermieden.

Resultat:

Das Resultat sieht wie folgt aus:

fortlaufender Saldo
fortlaufender Saldo

Nun muss noch die Indexspalte gelöscht und der Datentyp für die Spalte [Saldo] angepasst werden, und fertig ist die Tabelle mit dem fortlaufenden Saldo.

Anbei noch der Code.

2. Gruppierter Saldo bilden

Die gezeigte Lösung für den fortlaufenden Saldo werden wir später noch für den gruppierten Saldo benötigen. Leider kann ich aber mit Hilfe der Gruppierungsfunktion aus der Spalte [Saldo] nicht direkt einen gruppierten Saldo erstellen. Dazu benötigen wir die Unterstützung von “M”, der Codesprache hinter Power Query.

Der erste Schritt ist wieder eine neue Abfrage zu erstellen, welche auf die Grundabfrage “Lagerbewegungen” verweist.

Danach wird die Tabelle nach Produkt gruppiert. Als Aggregation habe ich “Alle Zeilen” gewählt und die neue Spalte als “Daten” benannt. Dieser Schritt kann bequem über die Oberfläche erstellt werden.

Daten gruppieren
Daten gruppieren

Der M-Code dahinter sieht wie folgt aus:

gruppierter_Saldo = Table.Group(Source,{"Produkt"}, {{"Daten", each _, type table}}),

Damit haben wir den Kopf des Befehls gebildet. Nun müssen wir Power Query noch dazu bringen, dass die neue Spalte [Daten] auch wie gewünscht gruppiert und der Saldo gebildet wird.

Dazu müssen wir den letzten Teil des Befehls, “each _ “ , anpassen und eine neue erschaffene Tabelle als Input liefern. Dazu verwenden wir den Code aus dem ersten Beispiel, passen ihn leicht an und wandeln ihn in eine Funktion um.

Funktion erstellen

Der erste Schritt ist, die Datentabelle in eine Inputvariable zu ändern.

Dies geschieht mit

(Input as table) =>

Die nächsten drei Schritte bleiben unverändert.

Sorting =         Table.Sort(Quelle,{{"Datum", Order.Ascending}}),
added_Index =      Table.AddIndexColumn(Sorting, "Index", 1, 1),
cumulativ_total =  Table.AddColumn(
added_Index, "Saldo", each
List.Sum(List.Range(added_Index[Betrag],0,[Index])
)),
ch_Type =         Table.TransformColumnTypes(cumulativ_total,{{"Saldo", Int64.Type}}),
removed_column =  Table.RemoveColumns(ch_Type,{"Index"})
in
removed_column

Als Resultat für die Gruppierung wollen wir nur eine Spalte zurückerhalten. Daher erstellen wir aus der Spalte [Saldo] eine Liste.

extract_total = cumulativ_total[Saldo]

Eine Liste kann nicht unformatiert werden, daher entfällt auch die Festlegung des Datentyps sowie des letzten Schritts, da wir ja nur noch eine Spalte respektive eine Liste haben.

Der gesamte Code der Funktion “fn_cumulative_Total” sieht wie folgt aus.

neue Funktion in Gruppierung verwenden

Nun ändern wir den letzten Teil der erstellten Gruppierungsfunktion um.

Anstelle von “each _” schreiben wir:

// Function Call
(Input as table) as table => // data for function
let
Call_Function = fn_cumulative_Total(Input),
// End of Function Call


Das zurückgegebene Resultat, der fortlaufende Saldo, muss noch in eine Spalte umgewandelt und mit den anderen Spalten zusammengebracht werden.

result = Table.FromColumns(
Table.ToColumns(Input){Call_Function},
Value.Type(Table.AddColumn(
Input, "Saldo", each null, type number)))
in
result

Jetzt haben wir eine gruppierte Tabelle enthalten, welche in der Spalte [Daten] das Resultat enthält:

Nach Erweiterung der Spalte [Daten] sehen wir, dass die Funktion  alle Bewegungen eines Produktes fortlaufend summiert. Sobald ein neues Produkt beginnt, wird neu summiert.

gruppierter, fortlaufender Saldo pro Produkt
gruppierter, fortlaufender Saldo pro Produkt

und hier noch der vollständige Code für die Bildung des gruppierten, fortlaufenden Saldos.

Fazit

Unter Zuhilfenahme von “M” Code können bestehende Funktionen wie die Gruppierung erweitert und angepasst werden. Ich hoffe, der Artikel hilft Euch bei solchen und ähnlichen Fragestellungen weiter.

Mehr Artikel über Power Query findet Ihr im Power Query Online Buch.

Happy Querying!

Liebe Leser, ich schreibe meine Artikel für Euch. Es ist mein Bestreben, Euch qualitativ ansprechenden Inhalt zu liefern. Nehmt Euch bitte eine Minute Zeit und gebt mir nachstehend eine Bewertung ab oder schreibt einen Kommentar.
Besten Dank.

Kommentar verfassen

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.

%d Bloggern gefällt das: