Splitting up events by shift start times in Power Query Editor

[JohnH-Crown]

I have a report that displays event duration data for a manufacturing facility that runs three shifts around the clock and events can span multiple shifts.

The current request I have from my users is to add a shift slicer to the reports which require splitting event records with defined start and end timestamps into multiple records based on when each shift starts.

Here is a simplification of where I have the processing right now in the Power Query Editor. Each record has when the event starts and ends, and then when each shift starts for that day. (At this stage the records have already been split between days so records spanning days is not a concern)

Event	Date	EventStart	EventEnd	FirstShiftStart	SecondShiftStart	ThirdShiftStart
A	8/5/2021	8/5/2021 04:01	8/5/2021 23:50	8/5/2021 06:15	8/5/2021 16:30	8/5/2021 22:00

Here is the output I would like to get to. I know I can create a column to calculate the shift, I just need help to get to the multiple records that are contained within each shift.

Event	Date	EventStart	EventEnd	Shift
A	8/5/2021	8/5/2021 04:01	8/5/2021 06:15	Third
A	8/5/2021	8/5/2021 06:15	8/5/2021 16:30	First
A	8/5/2021	8/5/2021 16:30	8/5/2021 22:00	Second
A	8/5/2021	8/5/2021 22:00	8/5/2021 23:50	Third

Some events may already be fully contained within a shift, and others may only span two shifts. Any assistance is greatly appreciated!

[AlexisOlson]

See if this helps get you going in the right direction:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbLQN9U3MjAyRGIqGJhYGaAIGBlbmRqgqDCzMjRFFjA0szJGUWFkZGVgoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Event = _t, Date = _t, EventStart = _t, EventEnd = _t, FirstShiftStart = _t, SecondShiftStart = _t, ThirdShiftStart = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"Date", type date}, {"EventStart", type datetime}, {"EventEnd", type datetime}, {"FirstShiftStart", type datetime}, {"SecondShiftStart", type datetime}, {"ThirdShiftStart", type datetime}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Event", "Date"}, "Label", "Event Start"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Event", Order.Ascending}, {"Date", Order.Ascending}, {"Event Start", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index0", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Event", "Date", "Index1"}, #"Added Index1", {"Event", "Date", "Index0"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Label", "Event Start"}, {"Next Label", "Event End"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Added Index1",{"Event", "Date", "Label", "Next Label", "Event Start", "Event End", "Index0", "Index1"})
in
    #"Reordered Columns"

[JohnH-Crown · Accepted solution]

Thanks for the reply @AlexisOlson! I was not able to get your approach to work, but after examining it, it inspired me to my solution!

TLDR; The approach that I found to work was to add a collection column for each 'shift period' and then derive a Start and End column based on the shift period, the Event Start/End, and the Shift start time columns.

The Details

First I added the ShiftPeriod column:

ShiftPeriod = {"PreFirstShift", "FirstShift", "SecondShift", "ThirdShift"}

Then I expanded that column so every record is multiplexed with these periods.

After that, I added a Start custom column with this logic:

Start = if [ShiftPeriod] = "PreFirstShift" then
  if [RecordStart] < [FirstShiftStart] then
    [RecordStart]
  else
    ""
else if [ShiftPeriod] = "FirstShift" then
  if [RecordStart] < [FirstShiftStart] then
    [FirstShiftStart]
  else if [RecordStart] < [SecondShiftStart] then
    [RecordStart]
  else
    ""
else if [ShiftPeriod] = "SecondShift" then  
  if [RecordStart] < [SecondShiftStart] then
    [SecondShiftStart]
  else if [RecordStart] < [ThirdShiftStart] then
    [RecordStart]
  else
    ""
else
  if [RecordStart] < [ThirdShiftStart] then
    [ThirdShiftStart]
  else
    [RecordStart]

Which determines the appropriate start date/time for that record, depending on the shift period that the record is intended for.

Then I did something similar for an End custom column:

End = if [ShiftPeriod] = "PreFirstShift" then
  if [RecordEnd] < [FirstShiftStart] then
    [RecordEnd]
  else
    [FirstShiftStart]
else if [ShiftPeriod] = "FirstShift" then
  if [RecordEnd] < [FirstShiftStart] then
    ""
  else if [RecordEnd] < [SecondShiftStart] then
    [RecordEnd]
  else
    [SecondShiftStart]
else if [ShiftPeriod] = "SecondShift" then
  if [RecordEnd] < [SecondShiftStart] then
    ""
  else if [RecordEnd] < [ThirdShiftStart] then
    [RecordEnd]
  else
    [ThirdShiftStart]
else
  if [RecordEnd] < [ThirdShiftStart] then
    ""
  else 
    [RecordEnd]

This will leave records that the Event Start/End don't overlap with a null or empty string in either the Start or End columns, which I filtered out, and changed the column types to Date/Times. Then cleaned up the no longer needed columns.