03.11.2020

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

Business Value

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).

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.  
  5. var Maschinenname_AktuelleZeile = df[Maschinenname]
  6. var Zeit_AktuelleZeile =df[Zeit] // alternative EARLIER verwenden
  7. return
  8. /* 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*/
  9.  
  10. CALCULATE(
  11.     COUNTROWS(df),
  12.     FILTER(
  13.         df,
  14.         df[Maschinenname]= Maschinenname_AktuelleZeile && // Filter auf alle Zeilen der aktuellen Maschine
  15.         df[Zeit] <= Zeit_AktuelleZeile // Alle Zeilen die einen kleineren Zeitstempel
  16.         // als die aktuelle Zeilen haben. Der frühste Zeitstempel erhält die 1
  17.         )
  18. )

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.  
  4. LOOKUPVALUE(
  5.             df[Temperatur], // Die Spalte enthält die anzufügenden Wertr
  6.             df[Maschinenname], // Wichtig hier nur nötig da wir den Index mehrfach vergeben haben
  7.             df[Maschinenname], // d.h. die Position 1 gibt es für Maschine A und Maschine B
  8.             df[Index (DAX)], // Der von uns erzeuge Index
  9.             (df[Index (DAX)]-1) // Die vorherige Indexposition, also die Zeile wo der Wert zu finden ist
  10. )
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.

Banner, das auf Power BI Webinare verweist

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.

Klartext

  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden