Wasserfall-Diagramm mit Power Query

Wasserfall-Diagramm mit Power Query

Unser heutiges Ziel ist ein Wasserfall-Diagramm wie es im oberen Bereich zu sehen ist. Inhalte hierbei sind ein Startpunkt, verschiedene Bewegungen und eine Endsumme. Jede Bewegung startet mit dem Ende der vorherigen Bewegung. Fällt die Bewegung, ist diese rot gekennzeichnet, steigt sie, erkennt man sie am kräftigen Grün.

In der ursprünglichen Datenquelle für ein solches Diagramm fehlt in der Regel die untere Ergebniszeile wie die in unserem Beispiel einer Gewinn- und Verlustrechnung:

Das macht es in der Regel etwas schwierig, diese Lösung in eine sich automatisch aktualisierende Excel-Berichtsmappe zu integrieren. Vor allem dann, wenn die Zeilen und Spalten dynamisch angelegt sind.

Und das ist die Lösung via Power Query: Kalkulation und Darstellung– vollautomatisch und dynamisch.

Updaten Sie einfach Ihre Datenquelle („SourceData“) und der Rest wird automatisch übernommen. In unserem simplen Beispiel wird die Reihenfolge der Quelldatentaballe in der Haupttabelle beibehalten. Einfach die Anfrage aktualisieren oder das Häkchen unter „Daten – alles aktualisieren“ aktivieren – fertig. Einfach die Tabelle entsprechend ändern (ja nachdem wie die Quelldaten vorliegen: Zahlen manuell einfügen, verknüpfen oder eine Power Query Abfrage erstellen…) – Daten – Alle aktualisieren – und fertig ist das neue Chart.

Die Power Query Abfrage, die das alles steuert, braucht dazu nicht angefasst oder angepasst werden. Kann mal also getrost vergessen.

[wpfilebase tag=file id=40 tpl=filebrowser /]

Wer aber wissen möchte, wie genau dieses Tool funktioniert – einfach weiterlesen! 🙂

let Source = Excel.CurrentWorkbook(){[Name="Sourcedata"]}[Content],
Dieser Schritt importiert die Datenquelle: Eine Tabelle namens „SourceData“.

AppendLastRow = Table.Combine({Source,WaterfallEnd}),
An dieser Stelle wird die letzte Zeile festgelegt, die wir einfach in PowerQuery eingefügt haben. (Query WaterfallEnd)

AddIndex = Table.AddIndexColumn(AppendLastRow, "Index", 0, 1),
Durch diesen Befehl wird eine Standard-Index-Spalte erstellt. Durch diese wird die Zeilennummer für die Kummulation bestimmt.

Cum = Table.AddColumn(AddIndex, "Cum", each List.Sum(List.FirstN(AppendLastRow[Amount],[Index]+1))),
Bei der Kummulation möchten wir, dass alle Beträge bis zur aktuellen Zeile addiert werden. Die Spalte mit dem Betrag („Amount“) gehen wir dabei wie folgt an: (verschachteltes Formular von innen nach außen lesen):

• AppendLastRow[Amount]. Liest sich wie folgt: In den eckigen Klammern steht der Name der entsprechenden Spalte, davor der Name der Tabelle. Wir suchen also nach der Tabelle des vorherigen Schrittes (AppendLastRow) und wählen die Spalte „Amount“ aus.
• Allerdings möchten wir nicht die gesamte Spalte zusammenaddieren, sondern nur die Zeilen bis zum aktuellen Index-Wert. Dafür nutzen wir List.FirstN(AppendLastRow[Amount],[Index]). Bestehend aus unserer frisch abgerufenen Spalte und [Index] für die ersten n Zeilen, die einbezogen werden sollen. Hierzu müssen wir nur eine 1 hinzufügen, denn der Index startet bei 0.
• Als letzten Schritt summieren wir das ganze dann mithilfe der List.Sum-Funktion.

CumPrev = Table.AddColumn(Cum, "CumPrev", each try(Cum[Cum]{[Index]-1}) otherwise null),
Für die „Fall“-Spalte unserer Tabelle brauchen wir das „Cum“ der vorherigen Zeile. Wie wir in den vorherigen Schritten gelernt haben, können wir jede Spalte unserer Schritte/Zwischenergebnisse gezielt ansprechen.

• Als erstes rufen wir eine bestimmte Zeile einer solchen Spalte ab, indem wir folgende Syntax anwenden:
  • Schrittname[Spaltenbezeichnung]{Zeilennummer}
• Dabei füllen wir die Zeilennummer dynamisch mit diesem kleinen Trick: 
  • [Index] unserer aktuellen Zeile minus 1 = Zeilennummer der vorherigen Zeile: [Index]-1
• Um den Fehler abzufangen, der bei der ersten Zeile entsehen würde, für die es ja keine vorherige Zeile gibt, klammern wir den Ausdruck noch in das WENNFEHLER-Äquivalent ein:
  • try (Ausdruck) otherwise “DefaultWert”.

ReplaceAmount = Table.ReplaceValue(CumPrev,null,each _[Cum],Replacer.ReplaceValue,{"Amount"}),
Die letzte Zeile, die wir angefügt haben, hat ja keinen Wert in der „“Amount“-Spalte. Um dort jetzt den richtigen Wert reinzukriegen (der „Cum“-Wert der vorherigen Zeile), nutzen wir die Ersetzen-Funktion auf null-Werte, denn dieses Feld sollte die einzig leere Zelle in der Amount-Spalte sein: Das unscheinbar wirkende “_” in _[Cum] bedeutet, dass wir uns dabei auf den aktuellen Schritt beziehen.

Type = Table.AddColumn(ReplaceAmount, "Type", each if [Index]=0 then "Start" else if [Index] = List.Max(ReplaceAmount[Index]) then "End" else if Number.Sign([Cum])<> Number.Sign([CumPrev]) then "Cross" else ""),
Dieser Schritt besteht hauptsächlich aus vielen WENN-DANNs, deren Syntax sehr an Excel angelehnt ist. Das erklärt sich eigentlich beim Lesen von selbst.

Das gilt auch für die anderen Spalten:
Fall = Table.AddColumn(Type, "Fall", each if [Type] = "Start" then 0 else if [Type] = "Cross" then [CumPrev] else if Number.Sign([CumPrev])<>Number.Sign([Amount]) then -[Amount] else 0),

Rise = Table.AddColumn(Fall, "Rise", each [Amount]+[Fall]),

Base = Table.AddColumn(Rise, "Base", each if [Type] = "Cross" or [Type] = "End" or [Type] = "Start" then 0 else if [Fall]<>0 then [Cum] else [CumPrev]),

RemoveOtherCols = Table.SelectColumns(Base,{"Waterfall", "Base", "Fall", "Rise", "Amount"}),
Ein kleiner Trick beim Entfernen der Spalten, die wir nicht benötigen: Wir haben die restlichen Spalten neu angeordnet indem wir die Namen in die gewünschte Reihenfolge gebracht haben. Dadurch spart man sich einen zusätzlichen Schritt.

ChgType = Table.TransformColumnTypes(RemoveOtherCols,{{"Amount", type number}, {"Rise", type number}, {"Fall", type number}, {"Base", type number}})
Als letzten Schritt müssen wir das richtige Zahlenformat auswählen. Das sorgt für eine stabile Formatierung in Excel.

[wpfilebase tag=file id=40 tpl=filebrowser /]

Viel Spaß & STAY QUERYIOUS! 🙂

Dieser Eintrag wurde von unserer Autorin Imke Feldmann auch auf thebiccountant.com veröffentlicht.