In vielen Datenanalysen ist die effiziente Analyse von Zeitdaten entscheidend. Wenn es um die Auswertung von Zeiträumen geht, sollen diese häufig definierten Zeitmarkierungen zugeordnet werden. Diese Herausforderung tritt häufig bei der Verarbeitung von Zeitdaten von Mitarbeitern, Projekten, Events oder der Analyse von Maschinen- und Sensordaten auf.
Für eine effiziente Analyse in Power BI oder Excel kann es sinnvoll sein für Start- und Endzeiten, die sich über mehrere Tage und definierte Zeitmarkierungen erstrecken mit Power Query zusätzliche Datenzeilen zu generieren. In unserem Beispiel wählen wir einen Schichtplan, dem die Daten genau zugeordnet werden sollen.
Ausgangssituation
Die Datenquelle liefert für eine Start- und Endzeit eine Statusinformation. In dem Zeitraum zwischen Start- und Endzeit können Tages- und Schichtwechsel beinhaltet sein.
Der Schichtplan enthält eine Sortierspalte, die die erforderlichen Transformationsschritte vereinfacht. Angesichts der Tatsache, dass Power BI beim Datentyp Datetime oder Time auch Nachkommastellen für Sekunden unterstützt, wird das Schichtende in der Tabelle präzise mit 59.9999 Sekunden markiert. Diese Vorgehensweise garantiert eine eindeutige und überschneidungsfreie Zuweisung der Schichtzeiten.
Schritt 1: Zeilen für Tageswechsel generieren
Importiere zunächst beide Tabellen als Abfrage in Power Query und definiere die Datentypen.
Anschließend erstelle eine Custom Column. Zur Generierung einer Liste von Daten wird hier die Funktion List.Dates() verwendet.
Verwende für die Custom Column den folgenden M-Code:
=List.Dates(Date.From([Start]), Duration.Days(Date.From([Ende])-Date.From([Start]))+1, #duration(1,0,0,0))
Erläuterung:
List.Dates(start as date, count as number, step as duration)
Date.From([Start]): Die Spalte Start enthält das Startdatum. Da diese den Datentyp Datetime hat wird diese mit der Funktion Date.From() in ein Datum umgewandelt.
Duration.Days(Date.From([Ende])-Date.From([Start]))+1: Die Anzahl der zu generierenden Steps ist die Differenz von End- und Startdatum +1, auch hier muss wieder Start und Ende mit Date.From() in ein Datum konvertiert werden.
#duration(1,0,0,0)) : M-Syntax: #duration(days, hours, minutes, seconds), hier wird also ein Eintrag pro Tag erstellt.
Die neue Spalte „List“ enthält je Zeile eine Liste. In dem Datensatz mit einem Tageswechsel enthält diese Liste 2 Einträge von Start- bis Enddatum.
Expandiere nun die Spalte „List“. Für den Datensatz mit Tageswechsel erhältst du jetzt 2 Datenzeilen:
Mit 2 Bedingten Spalten kannst du anschließend Start und Ende anpassen. Bei der Datenzeile von Tag 1 wird das Ende auf 23:59:99.9999 Uhr geändert und Datenzeile von Tag 2 wird das Startdatum auf 00:00:00 festgelegt. Bei Datenzeilen ohne Tageswechsel bleiben die Zeitwerte erhalten.
= Table.AddColumn(#“Changed Type1″, „Start NEU“, each if [List]=Date.From([Start]) then [Start] else #datetime(Date.Year([List]), Date.Month([List]), Date.Day([List]), 00,00,00), type datetime)
= Table.AddColumn(#“Added Column – Start NEU“, „Ende NEU“, each if [List]=Date.From([Ende]) then [Ende] else #datetime(Date.Year([List]), Date.Month([List]), Date.Day([List]), 23,59,59.9999),type datetime)
Jetzt kannst du den ursprünglichen Start und Ende Spalten löschen und die Spaltennamen ändern.
Die transformierte Tabelle sieht dann wie folgt aus:
Jetzt haben wir Schritt 1 erledigt. Für diese Technik gibt es zahlreiche Anwendungsfälle (siehe Quellen).
Schritt 2: Zeilen für Schichtwechsel generieren
Jetzt wird es spannend! Nach dem Tageswechsel kommt nun der Schichtwechsel.
Hierzu separieren wir zunächst für Start und Ende jeweils Datum und Zeit in eine neue Spalte. Dies kannst du ganz einfach über das User Interfase erledigen
Spalte [Start] markieren -> Add Column -> Date -> Date Only
Die Umbenennung der Spalte in einen sprechenden Namen kannst du direkt im Power Query Code vornehmen:
= Table.AddColumn(#“Renamed Columns“, „Date“, each DateTime.Date([Start]), type date)
= Table.AddColumn(#“Renamed Columns“, „Start Datum“, each DateTime.Date([Start]), type date)
Jetzt füge für Start Zeit (im Menü unter Add Column -> Time -> Time only) und Ende Datum und Ende Zeit mit dem gleichen Vorgehen eine Spalte ein. Dies ist das Ergebnis:
Im nächsten Schritt identifizieren wir zunächst die Schicht, die zu Beginn des betrachteten Zeitraums gültig war, und anschließend alle Schichten, die bis zum Ende dieses Zeitraums relevant sind. Um diese Aufgabe effizient zu bewältigen, greifen wir auf eine Lookup-Technik zurück, die oft für Slowly Changing Dimensions in Power Query zum Einsatz kommt.
Eine sehr gute und ausführliche Erklärung zu dieser Technik findet ihr zum Beispiel unter ExcelIsFun: Data Modeling for Slowly Changing Dimension
In dieser Beitrag die Funktion wirklich sehr gut erklärt konzentriere ich mich hier auf die Anwendung der Methode in unserem Beispiel:
Füge mit fx einen Custom Step mit dem M-Code =Table.Buffer(Schichtplan) ein. Mit dieser Funktion wird der Schichtplan in dieser Query im „Zwischenspeicher“ abgelegt, so dass die Query nicht in jeder Zeile immer wieder auf den Schichtplan zurückgreifen muss (Stichwort: bessere Performance). Diesen Step benenne in „BufferedSchichtplan“ um.
Anschließend können wir mit fx einen weiteren Custom Step einfügen, in dem wir wieder auf den Vorgängerstep verweisen.
Im nächsten Step ermitteln wir die Schicht, in der der Start unseres Zeitraumes liegt. Hier zu verwenden wir folgende Custom Column mit dem Namen „Start Schicht“ und dem folgendem M-Code:
=(OT) => Table.SelectRows(BufferedSchichtplan, (IT) => OT[Start Zeit] >= IT[Beginn] and OT[Start Zeit] <= IT[Ende] )
Diese Funktion selektiert eine Tabelle aus dem Schichtplan mit der Zeile, in der die „Start Zeit“ zwischen Beginn und Ende einer Schicht liegen.
Mit Expand erweitere nun die Tabelle um die Spalten „Schicht“ und „Sort“ mit Prefix.
Nun füge bitte eine weitere Custom Column mit dem Namen „Ende Schicht“ und dem folgenden M-Code ein:
=(OT) => Table.SelectRows( BufferedSchichtplan, (IT) => IT[Sort] >= OT[Start Schicht.Sort] and IT[Beginn] <= OT[Ende Zeit])
Diese Funktion selektiert aus dem Schichtplan die Zeile oder die Zeilen aus, die zwischen Start und Ende Zeit liegen. Die Sortierungsspalte aus unserem Schichtplan vereinfacht die Selektion, in dem wir die Schichten auswählen, deren Index größer oder gleich, die der Start Schicht ist. Die so ermittelte Tabelle enthält nun für Zeiträume mit Schichtwechsel ein oder mehrere Zeilen.
Mit Expand erweitere nun die Tabelle um die Spalten „Schicht“ und „Beginn“ und „Ende“ mit Prefix. Der expandieren einer Tabelle mit mehr als 1 Zeile führt dazu, dass neue Datenzeile generiert werden.
Die Tabelle sieht jetzt wie folgt aus:
Wie auch schon in Schritt 1 lassen sich jetzt mit Hilfe von bedingten Spalten Start und Ende Zeit korrigieren:
= if [Start Schicht.Schicht] = [Ende Schicht.Schicht] then [Start Zeit] else [Ende Schicht.Beginn], type time)
= if [Start Schicht.Schicht] = [Ende Schicht.Schicht] and [Ende Zeit] < [Ende Schicht.Ende] then [Ende Zeit] else if [Ende Zeit]<= [Ende Schicht.Ende] then [Ende Zeit] else [Ende Schicht.Ende], type time)
Jetzt musst du die Tabelle nur noch „bereinigen“, d.h. nicht mehr benötigte Spalten löschen, Spaltennamen ändern und die Datentypen kontrollieren.
Hier die fertige Tabelle mit eingefügten Zeilen für Tages- und Schichtwechsel:
Fazit
Abschließend lässt sich festhalten, dass die vorgestellte Methode effektiv neue Zeilen mit Power Query für tägliche Wechsel sowie spezifische Uhrzeiten einfügt, was eine einfache Analyse und Visualisierung mit Power BI oder Excel ermöglicht. Allerdings kann es bei umfangreichen Datenmodellen durch den „Lookup“ im Schichtplan zu Performance-Herausforderungen kommen.
Eine vielversprechende Alternative liefert Lars Schreiber in seinem Tutorial Den Personaleinsatz mit Power BI reporten (Youtube). Hierbei werden mit der Funktion List.DateTimes() Datensätze für alle relevanten Zeitintervalle generiert. Zum Beispiel 1 Datensatz pro Minute, diese Datensätze werden dann über eine Dimensionstabelle Zeit der relevanten Zeitmarkierung zugeordnet.
Wenn du unsicher bist, welche Power Query Methode für deinen spezifischen Anwendungsfall am besten geeignet ist, zögere nicht, uns anzusprechen. Wir beraten dich gerne, um die optimale Lösung für deine Anforderungen zu finden.
Quellen:
Creating Values For Each Date Using Power Query Formula | Master Data Skills + AI (enterprisedna.co)
So kannst du Monatsbudget auf Arbeitstage verteilen (youtube)
Power BI: Add a row for every day in between two dates – YouTube