BLOG

Power BI Tipps & Tricks: So fügen Sie den Wert der vorherigen Zeile ein

03.11.2020 Lukas Lötters

Power BI ist nicht Excel

Beipiel einer Excel-Tabelle, bei der mit einer Formel auf die vorherige Zeile refenrenziert wird.
Beispiel für eine einfache Excel-Formel, die auf den Wert einer vorherigen Zeile referenziert.

Index schafft Ordnung in Ihren Daten

Tabelle mit Temperaturwerten einer Maschine, die als Zeitreihe dargestellt werden
Temperaturwerte einer Maschine als Zeitreihe dargestellt (Ausschnitt).

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

  1. Index (DAX) =
  2. // Die Formel errechnet den Nachfolgewert pro Zeile im Datenmodell.
  3. // Um die Syntax zu vereinfachen erzeugen wir im Vorfeld Variablen, welche die jeweilige Maschine und den Zeitpunkt speichern
  4. var Maschinenname_AktuelleZeile = df[Maschinenname]
  5. var Zeit_AktuelleZeile =df[Zeit] // alternative EARLIER verwenden
  6. return
  7. /* 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*/
  8. CALCULATE(
  9.     COUNTROWS(df),
  10.     FILTER(
  11.         df,
  12.         df[Maschinenname]= Maschinenname_AktuelleZeile && // Filter auf alle Zeilen der aktuellen Maschine
  13.         df[Zeit] <= Zeit_AktuelleZeile // Alle Zeilen die einen kleineren Zeitstempel
  14.         // als die aktuelle Zeilen haben. Der frühste Zeitstempel erhält die 1
  15.         )
  16. )

Einfügen einer Index-Spalte mit Power Query

2. Gruppieren Sie die Tabelle nach dem Unterscheidungsmerkmal bzw. Maschinenname. Dabei ist wichtig, dass Sie „Alle Zeilen“ unter „Vorgang“ auswählen.

Gruppierung einer Tabelle in Power BI

3. Fügen Sie den Index als „Benutzerdefinierte Spalte“ ein.

Fenster für "Benutzerdefinierte Spalte" mit eingefügter Spaltenformel

4. Erweitern Sie das erzeugte Tabellen-Objekt.

Fenster für die Erweiterung des Tabellenobjektes

Hier der entsprechende M-Code in Kurzform:

  1. let
  2.     #“YourData“ = #“R-Source“,
  3.     #“Sorted Rows“ = Table.Sort(YourData,{{„Maschinenname“, Order.Ascending}, {„Zeit“, Order.Ascending}}),
  4.     #“Grouped Rows“ = Table.Group( #“Sorted Rows“, {„Maschinenname“}, {{„Data“, each _, type table [name=nullable text, zeit=datetime, Temperatur_1=number, Vibration=number]}}),
  5.     #“Added Custom“ = Table.AddColumn(#“Grouped Rows“, „Inhalt“, each Table.AddIndexColumn([Data], „Index“, 1, 1)),
  6.     #“Removed Columns2″ = Table.RemoveColumns(#“Added Custom“,{„Maschinenname“, „Data“}),
  7.     #“Expanded {0}“ = Table.ExpandTableColumn(#“Removed Columns2″, „Inhalt“, {„Maschinenname“, „Zeit“, „Temperatur“, „Vibration“, „Index“}, {„Maschinenname“, „Zeit“, „Temperatur“, „Vibration“, „Index“}),
  8.     #“Changed Type“ = Table.TransformColumnTypes(#“Expanded {0}“,{{„Temperatur“, type number}, {„Vibration“, type number}, {„Zeit“, type datetime}, {„Index“, Int64.Type}})
  9. in
  10.     #“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.

Tabelle mit eingefügter Index-Spalte
Tabelle mit Index-Spalte
  1. Vorherige Zeile in DAX (Datetime) =
  2. // Wir nutzten die Lookup Funktion, ähnlich zu einem SVERWEIS in Excel um mithilfe des Indexes die vorherige Zeile zu finden.
  3. LOOKUPVALUE(
  4.             df[Temperatur], // Die Spalte enthält die anzufügenden Wertr
  5.             df[Maschinenname], // Wichtig hier nur nötig da wir den Index mehrfach vergeben haben
  6.             df[Maschinenname], // d.h. die Position 1 gibt es für Maschine A und Maschine B
  7.             df[Index (DAX)], // Der von uns erzeuge Index
  8.             (df[Index (DAX)]-1) // Die vorherige Indexposition, also die Zeile wo der Wert zu finden ist
  9. )
Sortierte Zeitreihe mit Verweis auf vorherige Spalte
Die berechnete Spalte verweist nun auf den Wert der vorherigen Zeile, sodass ein Abgleich einfach möglich ist.
jetzt Teilen auf

Your email address will not be published. Required fields are marked *

Join #teamoraylispeople

Gestalte mit uns
die Welt der Daten