Fortlaufende Zähler je Kostenstelle erzeugen

In einem kleinen Kundenprojekt wurde ich vor kurzem mit einer Problemstellung konfrontiert, die hervorragend dafür geeignet ist, die Funktionsweise von Power Query zu erläutern. Sieh Dir an, wie ich mit Power Query auf einfache Art und Weise fortlaufende Zähler je Kostenstelle erzeugen kann.
Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!

Die Zielstellung

Die Zielstellung ist, aus der linken blauen Tabelle, die rechte grüne Tabelle er erzeugen:

Inhaltlich muss ich dabei auf folgendes achten:

1. Die Zieltabelle soll nach Kostenstelle und Bereich sortiert sein
2. Die neue Spalte „Zähler“ soll eine fortlaufende Nummer erzeugen, die je Kostenstelle erneut bei 1 beginnt.

Es gibt wie immer viele unterschiedliche Wege, die zum Ziel führen. Schauen wir uns gemeinsam meinen Lösungsansatz an.

Die Lösung

Meine Lösung enthält in Power Query 10 unterschiedliche Schritte. Ich gehe jetzt auf die wesentlichen davon ein.

Das Sortieren der Spalten

Nachdem die Basistabelle in Power Query geladen wurde, sortiere ich zunächst die Spalten Kst und Bereich. Hierbei ist die richtige Reihenfolge der Sortierung wichtig. Im folgenden Screenshot kannst Du an den markierten Ziffern 1 und 2 erkennen, dass zuerst die Spalte Kst sortiert wurde und im Nachgang die Spalte Bereich. Dies stellt sicher, dass alle gleichen Kostenstellen ohne Unterbrechung zusammenstehen und die Bereiche aufsteigend innerhalb ihrer Kostenstelle vorkommen.

Nachdem die Sortierung sichergestellt ist, folgt nun die erste Hilfsspalte.

Neue Spalte: Fortlaufenden Index hinzufügen

Über Spalte hinzufügen → Indexspalte → Von 0 füge ich eine neue Spalte hinzu, die eine fortlaufende Nummer für jede Zeile der Tabelle erzeugt. Dieser Index berücksichtigt den Wechsel von Kostenstellen explizit nicht.

Diese Indexspalte allein ist wenig hilfreich. In Kombination mit dem folgenden Schritt, wirst Du die Sinnhaftigkeit erkennen.

Neue Spalte: Ermittlung des ersten Auftretens der jeweiligen Kostenstelle

Die eben erzeugte Indexspalte ermöglicht das Identifizieren einer jeden Zeile innerhalb der Tabelle. Damit ich jedoch den gewünschten fortlaufenden Zähler je Kostenstelle ermitteln kann, erzeuge ich eine weitere Hilfsspalte über die Funktion: List.PositionOf(#"Hinzugefügter Index"[Kst], [Kst]).

Diese Spalte ist der eigentliche Knackpunkt der Lösung. Daher erkläre ich kurz, was hier geschieht:

• Allgemeine Erklärung der Funktion List.PositionOf(): List.PositionOf() ist eine Funktion, die in ihrer kürzesten Form die folgende Syntax hat: List.PositionOf(list as list, value as any) as any. Im ersten Argument list  übergebe ich der Funktion eine Liste an Werten. Innerhalb dieser Liste kann ich dann nach der Position des zweiten Arguments value suchen. Tritt dieser Wert gar nicht auf, so erhalte ich den Wert -1, ansonsten wird mir die null-basierte Position des Wertes innerhalb der Liste zurückgegeben.
• Ermittlung des zweiten Arguments value: Value ist das Argument welches besagt, welchen Wert wir in der Liste suchen wollen. Innerhalb einer kalkulierten Spalte in Power Query, kann ich einfach mit [Kst] auf den aktuellen Zeilenwert der Spalte Kst verweisen. Somit ist [Kst] mein Wert für das Argument value.
• Ermittlung des ersten Arguments list: Das erste Argument ist weniger intuitiv zu referenzieren. Daher beschreibe ich es erst an zweiter Stelle. Um die Liste zu referenzieren, in welcher die erste Position des aktuellen Zeilenwertes für [Kst] gesucht werden soll, muss ich den Namen des letzten Power Query-Schrittes wählen und diesem die Spaltenbezeichnung in eckigen Klammern hinten anstellen. In meinem Beispiel sieht das so aus: #"Hinzugefügter Index"[Kst]. Falls Dich der Umgang mit Listen tiefgehender interessiert, empfehle ich Dir meinen englischsprachigen Artikel über Listen.

Da auch diese Spalte eine reine Hilfsspalte ist, komme ich nun zum schlussendlichen Ziel: Der Zählerspalte.

Die finale Zählerspalte erzeugen

Wenn Du einen genauen Blick auf die Spalten Index und Hilf wirfst, wirst Du schnell erkennen, dass die Differenz beider Spalten +1 exakt das ist, was wir für unsere Lösung benötigen:

Am Ende entferne ich alle nun überflüssigen Hilfsspalten und erhalte das gewünschte Ergebnis:

Ich hoffe, dass diese Lösung für Dich nützlich ist 🙂

Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de

Viele Grüße aus Hamburg,

Lars