Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JohnH-Crown
Frequent Visitor

Splitting up events by shift start times in Power Query Editor

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)

EventDateEventStartEventEndFirstShiftStartSecondShiftStartThirdShiftStart
A8/5/20218/5/2021 04:018/5/2021 23:508/5/2021 06:158/5/2021 16:308/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.

EventDateEventStartEventEndShift
A8/5/20218/5/2021 04:018/5/2021 06:15Third
A8/5/20218/5/2021 06:158/5/2021 16:30First
A8/5/20218/5/2021 16:308/5/2021 22:00Second
A8/5/20218/5/2021 22:008/5/2021 23:50Third

 

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

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

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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"

AlexisOlson_0-1643409903609.png

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.