Forum Discussion
Splitting up events by shift start times in Power Query Editor
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.
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"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.