This post is also available in:
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.
Danach folgt bereits das Herzstück des Codes, die Bildung des fortlaufenden Saldos.
Gebt dazu folgenden Code in eine neue, benutzerdefinierte Spalte ein:
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:
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.
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.
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.
