Die Einsatzmöglichkeiten von Power BI sind bekanntlich vielfältig und reichen weit über Ad-hoc-Analysen, Reporting und Visualisierungen hinaus. Beispielsweise wird Microsofts Cloud-Dienst auch gerne zur Visualisierung und Auswertung von Mess- bzw. Zeitreihen eingesetzt, die auf den Datenströmen von Sensoren basieren. Eine der Kernfragen lautet hierbei, wie stark sich die Sensorwerte zwischen zwei Messpunkten verändern – also zum Beispiel: Um wie viel ist die Arbeitstemperatur einer Maschine seit der letzten Messung gestiegen?
Damit Sie Werte in Power BI effektiv abgleichen können, bietet es sich an, jeder Zeile der Zeitreihe den Wert des vorherigen Messzeitpunktes hinzuzufügen. Bei einer sortierten Zeitreihe handelt es sich somit um den Wert der vorangegangenen Zeile. Allerdings kommen Sie bei dieser Aufgabenstellung mit dem bekannten Excel-Wissen nicht weiter. Deshalb möchte ich in diesem Beitrag einen Lösungsweg aufzeigen, mit dem sich Messwerte in einzelnen Zeilen relativ einfach ergänzen und in der Folge vergleichen lassen.
Power BI ist nicht Excel
Aber was ist eigentlich das Problem? Schließlich ist das Hinzufügen von Werten aus der vorherigen Zeile – der „Previous Row“ – für den eingefleischten Excel-User eine Standardaufgabe. Ebenso lässt sich in Excel ganz einfach eine Formel erstellen, die die Differenz zwischen dem vorangegangenen und dem aktuellen Messwert zeigt.
Leider können Sie dieses Vorgehen nicht auf Power BI anwenden. Der Grund hierfür liegt in der Organisation der Daten: Power BI speichert diese nicht – wie Excel – in einer einfachen Tabelle, sondern in einem spaltenorientierten, komprimierten Datenmodell. Die einzelnen Zeilen des Datenmodells besitzen also rein technisch gar keine „vorherige Zeile“. Eine natürliche Sortierung gibt es nicht mehr.
Das heißt aber nicht, dass Sie in Power BI ohne eine Referenzierung auf vorangegangene Werte auskommen müssen. Tatsächlich bieten sich hier verschiedene Möglichkeiten. Eine davon werde ich nun auf Basis von ein paar Demodaten skizzieren.
Index schafft Ordnung in Ihren Daten
Bei meinen Demodaten handelt es sich um Temperaturwerte von zwei Maschinen, die ich als Zeitreihe simuliert habe. Die Abtastrate beträgt eine Sekunde. Allerdings habe ich Messungen entfernt und damit Lücken in den Daten geschaffen, wie sie in der Realität immer wieder auftreten.
Um nun die Differenz zwischen jedem Messwert und seinem direkten Vorgänger zu bestimmen, müssen Sie Ihre Daten zunächst sortieren. Zu diesem Zweck erstellen Sie eine Hilfsspalte bzw. einen Index, der die Reihenfolge pro Maschine und den Messungen untereinander angibt. Mithilfe dieses Index können Sie genau den zeitlichen Vorgängerwert zu einer beliebigen Zeile darstellen. Beispielsweise hat der erste Messwert einer Maschine die Indexnummer 1, der direkte zeitliche Nachfolger die Indexnummer 2.
Eine solche Index-Spalte können Sie in DAX, in Power Query oder am besten direkt in der Quelle, wie z.B. einer Datenbank, erzeugen. Im Folgenden möchte ich auf die möchte ich auf die Power-BI-Varianten DAX und Power Query genauer eingehen.
Einfügen einer Index-Spalte mit DAX
Mit DAX lässt sich eine Index-Spalte im Datenmodell relativ einfach anlegen. Sie können hierfür die untenstehende Formel verwenden. Dabei gilt es zu beachten, dass unsere Datenreihe zwei Maschinen A und B umfasst. Entsprechend muss der Index auch zwischen den Messwerten dieser beiden Varianten unterscheiden:
-
Index (DAX) =
-
// Die Formel errechnet den Nachfolgewert pro Zeile im Datenmodell.
-
// Um die Syntax zu vereinfachen erzeugen wir im Vorfeld Variablen, welche die jeweilige Maschine und den Zeitpunkt speichern
-
var Maschinenname_AktuelleZeile = df[Maschinenname]
-
var Zeit_AktuelleZeile =df[Zeit] // alternative EARLIER verwenden
-
return
-
/* Die Logik ist wie folgt, um die Position einer Zeile innerhalb des Index zu bestimmen, zählen wir wie viele Messwerte einer Maschine kleiner sind, als der Wert der jeweiligen Zeile. Z.B. der zeitlich erste Messewert der Zeitreihe besitzt keinen Messewert der kleiner ist als er selbst dementsprechend bekommt er die Indexnummer 1. Für den zeitlich zweiten Messwert einer Maschine finden wir 1 größeren Messerwert er bekommt die Indexnummer 2*/
-
CALCULATE(
-
COUNTROWS(df),
-
FILTER(
-
df,
-
df[Maschinenname]= Maschinenname_AktuelleZeile && // Filter auf alle Zeilen der aktuellen Maschine
-
df[Zeit] <= Zeit_AktuelleZeile // Alle Zeilen die einen kleineren Zeitstempel
-
// als die aktuelle Zeilen haben. Der frühste Zeitstempel erhält die 1
-
)
-
)
Falls neben den Maschinennamen weitere Felder für die Gruppierung des Indexes verwendet werden sollen, können diese im Variablenbereich der Formel sowie im Filter der CALCULATE-Funktion eingefügt werden.
Achtung: Die Berechnung in DAX kann bei vielen Zeilen und bei Datentypen wie „Datetime“ oder „Dezimalzahlen“ sehr rechenintensiv sein. Die Operation lässt sich nicht abbrechen. Daher sollte die Power-BI-Mappe vorher gespeichert werden. Etwas schneller, dafür aber im Aufbau aufwändiger, ist eine Lösung auf Basis von Power Query.
Einfügen einer Index-Spalte mit Power Query
Bei Power Query wird die Indexspalte für jede Maschine zum Zeitpunkt der Beladung in das Datenmodell eingefügt. Hier die einzelnen Schritte:
1. Sortieren Sie die Tabelle nach dem Gruppierungsmerkmal – in unserem Fall der Maschinenname – sowie dem Zeitstempel.
2. Gruppieren Sie die Tabelle nach dem Unterscheidungsmerkmal bzw. Maschinenname. Dabei ist wichtig, dass Sie „Alle Zeilen“ unter „Vorgang“ auswählen.
3. Fügen Sie den Index als „Benutzerdefinierte Spalte“ ein.
4. Erweitern Sie das erzeugte Tabellen-Objekt.
Hier der entsprechende M-Code in Kurzform:
-
let
-
#“YourData“ = #“R-Source“,
-
#“Sorted Rows“ = Table.Sort(YourData,{{„Maschinenname“, Order.Ascending}, {„Zeit“, Order.Ascending}}),
-
#“Grouped Rows“ = Table.Group( #“Sorted Rows“, {„Maschinenname“}, {{„Data“, each _, type table [name=nullable text, zeit=datetime, Temperatur_1=number, Vibration=number]}}),
-
#“Added Custom“ = Table.AddColumn(#“Grouped Rows“, „Inhalt“, each Table.AddIndexColumn([Data], „Index“, 1, 1)),
-
#“Removed Columns2″ = Table.RemoveColumns(#“Added Custom“,{„Maschinenname“, „Data“}),
-
#“Expanded {0}“ = Table.ExpandTableColumn(#“Removed Columns2″, „Inhalt“, {„Maschinenname“, „Zeit“, „Temperatur“, „Vibration“, „Index“}, {„Maschinenname“, „Zeit“, „Temperatur“, „Vibration“, „Index“}),
-
#“Changed Type“ = Table.TransformColumnTypes(#“Expanded {0}“,{{„Temperatur“, type number}, {„Vibration“, type number}, {„Zeit“, type datetime}, {„Index“, Int64.Type}})
-
in
-
#“Changed Type“
Ich habe die Beschreibung bewusst kurz gehalten. Wenn Sie eine detaillierte Klick-Anleitung wünschen, kann ich Ihnen den Blog-Beitrag Number rows by group using Power Query ans Herz legen.
Werte aus vorheriger Zeile übertragen
Mithilfe der Indexspalte können Sie nun die „vorherigen Zeilen“ entlang des Indexes finden.
Hierfür nutzen Sie wiederrum DAX sowie eine berechnete Spalte:
-
Vorherige Zeile in DAX (Datetime) =
-
// Wir nutzten die Lookup Funktion, ähnlich zu einem SVERWEIS in Excel um mithilfe des Indexes die vorherige Zeile zu finden.
-
LOOKUPVALUE(
-
df[Temperatur], // Die Spalte enthält die anzufügenden Wertr
-
df[Maschinenname], // Wichtig hier nur nötig da wir den Index mehrfach vergeben haben
-
df[Maschinenname], // d.h. die Position 1 gibt es für Maschine A und Maschine B
-
df[Index (DAX)], // Der von uns erzeuge Index
-
(df[Index (DAX)]-1) // Die vorherige Indexposition, also die Zeile wo der Wert zu finden ist
-
)
Sie können nun also Bezüge herstellen, die das Datenmodell in Power BI ursprünglich nicht bietet. Die neue Spalte enthält eben jenen Messwert, den viele Nutzer den „Wert der vorherigen Zeile“ nennen. Weiterführend können Sie auch Abweichungen – die sogenannten Deltas – absolut oder relativ berechnen.
Noch eine Anmerkung: Die dargestellte Lösung liefert ausschließlich einen statischen Verweis von einer Zeile auf den vorherigen Wert einer Zeitreihe. Wenn Sie statt des direkten Vorgängers beispielsweise den ersten und letzten Wert eines ausgewählten Intervalls benötigen, dann ist ein anderes Vorgehen gefragt.
Möchten auch Sie Power BI möglichst effektiv nutzen, um die Werte in Ihren Daten umfassend zu erschließen? Dann informieren Sie sich weiter unter Power BI oder laden Sie sich unser Whitepaper Power BI als moderne Analyseplattform herunter.
Kommentare (4)