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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mmurdock2020
Frequent Visitor

Grouping A Row Based on Multiple Conditions Met and then Aggregating Modified Rows Below

Hello, my name is Matthew. My goal is to classify a Date+Time range into 3 categories: Day, Maintenance, and Night. The issue is that day shifts run for 10 hours, night shifts run for 10 hours, and maintenance runs for 4 hours and my Date+Time range many times throughout my data list goes over the 24 hour mark. Due to this issue, I am hoping to aggregate rows beneath so that I may have multiple rows with the time split like so: table.png

 

Currently, a single row looks like this:

table 2.png

 

I have categorized shifts like this and I know how to make them a relationship in the Model View but I only know how to connect their data when using filters on the presentation view in Power BI. When it comes to using any of the coding sequences I am very new and do not know much. Below is a picture of how the shifts are categorized.

table 3.png

 

If anyone has any ideas please let me know. My thought process is that I somehow need to nest in a List that meets the conditions mentioend above then aggregate that list into rows and use Fill Down to fill in the rest potentially. I have no idea how to do that after spending several hours on YouTube and message boards attemptign to remedy this. Please let me know if I need to do any further explanations, thank you.

3 ACCEPTED SOLUTIONS
Syk
Super User
Super User

Not sure if this will help but you may want to use a time dimension table and create a relationship to it.
Here's the article that explains it.
I did add a column for you to determine which shift you're in if you want to use this script:

let
    Source = List.Times(#time(0,0,0),24*60*60,#duration(0,0,0,1)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),
    #"Inserted Hour" = Table.AddColumn(#"Changed Type", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Time]), Int64.Type),
    #"Inserted Second" = Table.AddColumn(#"Inserted Minute", "Second", each Time.Second([Time]), type number),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Second", "AM/PM", each if [Hour] < 12 then "a.m." else "p.m."),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"AM/PM", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Time", "Time - Copy"),
    #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"Time - Copy"}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns", "Hour", "Hour - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column1",{{"Hour - Copy", "Hour label"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Hour label", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Hour label"}),
    #"Inserted Prefix" = Table.AddColumn(#"Removed Columns1", "Prefix", each "0" & Text.From([Hour], "en-NZ"), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Prefix",{{"Prefix", "Hour Label"}}),
    #"Extracted Last Characters" = Table.TransformColumns(#"Renamed Columns2", {{"Hour Label", each Text.End(_, 2), type text}}),
    #"Inserted Prefix1" = Table.AddColumn(#"Extracted Last Characters", "Prefix", each "0" & Text.From([Minute], "en-NZ"), type text),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Prefix1",{{"Prefix", "Minute Label"}}),
    #"Extracted Last Characters1" = Table.TransformColumns(#"Renamed Columns3", {{"Minute Label", each Text.End(_, 2), type text}}),
    #"Inserted Prefix2" = Table.AddColumn(#"Extracted Last Characters1", "Prefix", each "0" & Text.From([Second], "en-NZ"), type text),
    #"Renamed Columns4" = Table.RenameColumns(#"Inserted Prefix2",{{"Prefix", "Second Label"}}),
    #"Extracted Last Characters2" = Table.TransformColumns(#"Renamed Columns4", {{"Second Label", each Text.End(_, 2), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Last Characters2", "Index", 0, 1),
    #"Renamed Columns5" = Table.RenameColumns(#"Added Index",{{"Index", "TimeKey"}}),
    #"Inserted Modulo" = Table.AddColumn(#"Renamed Columns5", "Modulo", each Number.Mod([Hour], 12), type number),
    #"Renamed Columns6" = Table.RenameColumns(#"Inserted Modulo",{{"Modulo", "Hour Bin 12"}}),
    #"Inserted Integer-Division" = Table.AddColumn(#"Renamed Columns6", "Integer-Division", each Number.IntegerDivide([Hour], 8), Int64.Type),
    #"Renamed Columns7" = Table.RenameColumns(#"Inserted Integer-Division",{{"Integer-Division", "Hour Bin 8"}}),
    #"Inserted Integer-Division1" = Table.AddColumn(#"Renamed Columns7", "Integer-Division", each Number.IntegerDivide([Hour], 6), Int64.Type),
    #"Renamed Columns8" = Table.RenameColumns(#"Inserted Integer-Division1",{{"Integer-Division", "Hour Bin 6"}}),
    #"Inserted Integer-Division2" = Table.AddColumn(#"Renamed Columns8", "Integer-Division", each Number.IntegerDivide([Hour], 4), Int64.Type),
    #"Renamed Columns9" = Table.RenameColumns(#"Inserted Integer-Division2",{{"Integer-Division", "Hour Bin 4"}}),
    #"Inserted Integer-Division3" = Table.AddColumn(#"Renamed Columns9", "Integer-Division", each Number.IntegerDivide([Hour], 3), Int64.Type),
    #"Renamed Columns10" = Table.RenameColumns(#"Inserted Integer-Division3",{{"Integer-Division", "Hour Bin 3"}}),
    #"Inserted Integer-Division4" = Table.AddColumn(#"Renamed Columns10", "Integer-Division", each Number.IntegerDivide([Hour], 2), Int64.Type),
    #"Renamed Columns11" = Table.RenameColumns(#"Inserted Integer-Division4",{{"Integer-Division", "Hour Bin 2"}}),
    #"Inserted Integer-Division5" = Table.AddColumn(#"Renamed Columns11", "Integer-Division", each Number.IntegerDivide([Minute], 30), Int64.Type),
    #"Multiplied Column" = Table.TransformColumns(#"Inserted Integer-Division5", {{"Integer-Division", each _ * 30, type number}}),
    #"Renamed Columns12" = Table.RenameColumns(#"Multiplied Column",{{"Integer-Division", "Minute Bin 30"}}),
    #"Inserted Integer-Division6" = Table.AddColumn(#"Renamed Columns12", "Integer-Division", each Number.IntegerDivide([Minute], 15), Int64.Type),
    #"Multiplied Column1" = Table.TransformColumns(#"Inserted Integer-Division6", {{"Integer-Division", each _ * 15, type number}}),
    #"Renamed Columns13" = Table.RenameColumns(#"Multiplied Column1",{{"Integer-Division", "Minute Bin 15"}}),
    #"Inserted Integer-Division7" = Table.AddColumn(#"Renamed Columns13", "Integer-Division", each Number.IntegerDivide([Minute], 10), Int64.Type),
    #"Multiplied Column2" = Table.TransformColumns(#"Inserted Integer-Division7", {{"Integer-Division", each _ * 10, type number}}),
    #"Renamed Columns14" = Table.RenameColumns(#"Multiplied Column2",{{"Integer-Division", "Minute Bin 10"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns14", "Shift", each if [Time] >= #time(17, 0, 0) and [Time] < #time(21, 0, 0) then "Maintenance" else if [Time] >= #time(7, 0, 0) and [Time] < #time(17, 0, 0) then "Day" else if [Time] >= #time(21, 0, 0) or [Time] < #time(7, 0, 0) then "Night"
else "Unknown")
in
    #"Added Custom1"

View solution in original post

ronrsnfld
Super User
Super User

Given this data:

ronrsnfld_0-1720124076449.png

If you want this for results:

ronrsnfld_2-1720124140436.png

 

See my Shifts.pbix file.

Note it includes three queries

 

Shift Times

With Source Data set up a bit different from your example: 

ronrsnfld_3-1720124674871.png

 

 

let
    Source = Table.FromColumns({
        {#time(0,0,0),#time(7,0,0),#time(17,0,0),#time(21,0,0)},
        {#time(6,59,59), #time(16,59,59),#time(20,59,59),#time(23,59,59)},
        {"Night","Day","Maintenance","Night"}},
        type table[State Start Time=time, State End Time=time, Shift=text]),

//Add list of times by the second
//Times List needs to cover two days to account for shifts overlapping days
#"Times List" = Table.AddColumn(Source,"Times", (r)=> 
                    List.Sort(
                        List.Combine(
                            List.TransformMany(
                                List.DateTimes(
                                DateTime.From(r[State Start Time]), 
                                Duration.TotalSeconds(r[State End Time]-r[State Start Time]) + 1,
                                #duration(0,0,0,1)),
                            each {Date.AddDays(_,1)},
                            (x,y)=> {x,y})), Order.Ascending)                        
                            ,type {datetime}),
    #"Removed Columns" = Table.RemoveColumns(#"Times List",{"State Start Time", "State End Time"}),
    #"Expanded Times" = Table.ExpandListColumn(#"Removed Columns", "Times")
in
    #"Expanded Times"

 

 

fnShiftDateTimes

 

(startDt as date)=>

   Table.TransformColumns(
      ShiftTimes, 
      {"Times", 
         each DateTime.From(Number.From(startDt) + Number.From(_)),
      type datetime})

 

 

Shifts

Main Query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcrLCQAhDAXAViRnIXn5yJJWxP7bMC56HWZOCgZYRb3BMzwlqF/0ppbAodUnGb+IRKRaRWM9D1LpvfHeSCmXaj9V+66stQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"State Start Time", type nullable datetime}, {"State End Time", type nullable datetime}}),
    #"Add allTimes" = Table.AddColumn(#"Changed Type", "allTimes", each 
        List.DateTimes([State Start Time], Duration.TotalSeconds([State End Time]-[State Start Time])+1,#duration(0,0,0,1)),
            type {datetime}),
    #"Removed Columns" = Table.RemoveColumns(#"Add allTimes",{"State Start Time", "State End Time"}),
    #"Add Shifts" = Table.AddColumn(#"Removed Columns","Shifts", (r)=>
        let
            #"Expanded allTimes" = Table.Sort(Table.FromList(r[allTimes],Splitter.SplitByNothing(),{"allTimes"}),"allTimes"),
            #"Shift Times" = fnShiftDateTimes(Date.From(#"Expanded allTimes"[allTimes]{0})),
            #"Join" = Table.NestedJoin(#"Expanded allTimes","allTimes", #"Shift Times","Times","Joined",JoinKind.LeftOuter),
            #"Extract Shift" = Table.ExpandTableColumn(Join, "Joined", {"Shift"}, {"Shift"}),
            #"Grouped Rows" = Table.Group(#"Extract Shift", {"Shift"}, {
                {"Shift Start Time", each List.Min([allTimes]), type nullable datetime}, 
                {"Shift End Time", each List.Max([allTimes]), type nullable datetime}},
                GroupKind.Local)
        in 
            #"Grouped Rows"
        ),
    #"Removed Columns1" = Table.RemoveColumns(#"Add Shifts",{"allTimes"}),
    #"Expanded Shifts" = Table.ExpandTableColumn(#"Removed Columns1", "Shifts", {"Shift", "Shift Start Time", "Shift End Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Shifts",{{"Shift", type text}, {"Shift End Time", type datetime}})
in
    #"Changed Type1"

 

 

 

 

View solution in original post

dufoq3
Super User
Super User

Hi @mmurdock2020, I'm also adding another version of code 😉 --> this one is faster than my previous.

 

You can still edit shifts here:

dufoq3_0-1720796655766.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcAgDETRViLOkRhvWdwKov82YgJWcv16DNBasUpUGawbqZs6rOwr6sbiRCP1vRWpCcnJnCWgVB6OECjdke4YDAj2pmCXK0aZTCeLS4B0thqvtgaRi/wtyu94unzh7TjjNxP+4uVylt4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
    Custom1 = if UseRepeatValue = "Yes" then Table.Repeat(Source, RepeatValue) else Source,
    _Shifts = #table(type table[Shift=text, Start=time, End=time],
        { { "Night",       #time( 0,0,0), #time( 6,59,59) },
          { "Day",         #time( 7,0,0), #time(16,59,59) },
          { "Maintenance", #time(17,0,0), #time(20,59,59) },
          { "Night",       #time(21,0,0), #time(23,59,59) } }),
    BufferedShifts = List.Buffer(Table.ToRows(_Shifts)),
    StepBack = Custom1,
    ChangedType_US = Table.TransformColumnTypes(StepBack,{{"State Start Time", type datetime}, {"State End Time", type datetime}}, "en-US"),
    Ad_Helper = Table.AddColumn(ChangedType_US, "Helper", each 
        [ StartDate = Date.From([State Start Time]),
          StartTime = Time.From([State Start Time]),
          EndDate = Date.From([State End Time]),
          EndTime = Time.From([State End Time]),
          Dates = List.Dates(StartDate, Duration.TotalDays(EndDate-StartDate)+1, #duration(1,0,0,0)), //Buffer?
          DateTimes = List.Combine(List.Transform(Dates, (x)=> {{ x, if x = StartDate then StartTime else #time(0,0,0), if x = EndDate then EndTime else #time(23,59,59) }} )) //separate list for each day
       // Buffered = List.Buffer(DateTimes) //not needed
        ][DateTimes], type record),
    Ad_DateTimes = Table.AddColumn(Ad_Helper, "DateTimes", each Table.FromRows(List.Transform(List.Select(List.Combine(List.Transform([Helper], (h)=> 
        List.Accumulate(
                BufferedShifts,
                {},
                (s,c)=> s & { {c{0}} & {h{0}} & (    if h{0} > List.First([Helper]){0} and h{0} < List.Last([Helper]){0} then {c{1}, c{2}} else
                                                  { (if h{2} >= c{1} then if h{1} <= c{2} then if h{1} >= c{1} then h{1} else c{1} else null else null),
                                                    (if c{1} <= h{2} then if c{2} < h{1} then null else if h{2} <= c{2} then h{2} else c{2} else null) } ) }
        ))), (x)=> List.NonNullCount(x) = 4), (y)=> { y{0}, y{1} & y{2}, y{1} & y{3} }), type table[Shift=text, Start=datetime, End=datetime]), type table),
    DateTimes = Table.Combine(Ad_DateTimes[DateTimes]),
    GroupedRowsLocal = Table.Group(DateTimes, {"Start", "End"}, {
        {"Merged", each #table(type table[Shift=text, Start=datetime, End=datetime], {{Table.SelectColumns(Table.FirstN(_, 1), {"Shift"}){0}[Shift], List.Min([Start]), List.Max([End]) }}), type table }},
        GroupKind.Local,
        (x,y)=> Number.From( (Time.From(x[End]) <> #time(23,59,59) and Time.From(y[Start]) <> #time(0,0,0)) or Duration.TotalSeconds(y[Start]-x[End]) > 1 ) ),
    Merged = Table.Combine(GroupedRowsLocal[Merged])
in
    Merged

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

12 REPLIES 12
dufoq3
Super User
Super User

Hi @mmurdock2020, I'm also adding another version of code 😉 --> this one is faster than my previous.

 

You can still edit shifts here:

dufoq3_0-1720796655766.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcAgDETRViLOkRhvWdwKov82YgJWcv16DNBasUpUGawbqZs6rOwr6sbiRCP1vRWpCcnJnCWgVB6OECjdke4YDAj2pmCXK0aZTCeLS4B0thqvtgaRi/wtyu94unzh7TjjNxP+4uVylt4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
    Custom1 = if UseRepeatValue = "Yes" then Table.Repeat(Source, RepeatValue) else Source,
    _Shifts = #table(type table[Shift=text, Start=time, End=time],
        { { "Night",       #time( 0,0,0), #time( 6,59,59) },
          { "Day",         #time( 7,0,0), #time(16,59,59) },
          { "Maintenance", #time(17,0,0), #time(20,59,59) },
          { "Night",       #time(21,0,0), #time(23,59,59) } }),
    BufferedShifts = List.Buffer(Table.ToRows(_Shifts)),
    StepBack = Custom1,
    ChangedType_US = Table.TransformColumnTypes(StepBack,{{"State Start Time", type datetime}, {"State End Time", type datetime}}, "en-US"),
    Ad_Helper = Table.AddColumn(ChangedType_US, "Helper", each 
        [ StartDate = Date.From([State Start Time]),
          StartTime = Time.From([State Start Time]),
          EndDate = Date.From([State End Time]),
          EndTime = Time.From([State End Time]),
          Dates = List.Dates(StartDate, Duration.TotalDays(EndDate-StartDate)+1, #duration(1,0,0,0)), //Buffer?
          DateTimes = List.Combine(List.Transform(Dates, (x)=> {{ x, if x = StartDate then StartTime else #time(0,0,0), if x = EndDate then EndTime else #time(23,59,59) }} )) //separate list for each day
       // Buffered = List.Buffer(DateTimes) //not needed
        ][DateTimes], type record),
    Ad_DateTimes = Table.AddColumn(Ad_Helper, "DateTimes", each Table.FromRows(List.Transform(List.Select(List.Combine(List.Transform([Helper], (h)=> 
        List.Accumulate(
                BufferedShifts,
                {},
                (s,c)=> s & { {c{0}} & {h{0}} & (    if h{0} > List.First([Helper]){0} and h{0} < List.Last([Helper]){0} then {c{1}, c{2}} else
                                                  { (if h{2} >= c{1} then if h{1} <= c{2} then if h{1} >= c{1} then h{1} else c{1} else null else null),
                                                    (if c{1} <= h{2} then if c{2} < h{1} then null else if h{2} <= c{2} then h{2} else c{2} else null) } ) }
        ))), (x)=> List.NonNullCount(x) = 4), (y)=> { y{0}, y{1} & y{2}, y{1} & y{3} }), type table[Shift=text, Start=datetime, End=datetime]), type table),
    DateTimes = Table.Combine(Ad_DateTimes[DateTimes]),
    GroupedRowsLocal = Table.Group(DateTimes, {"Start", "End"}, {
        {"Merged", each #table(type table[Shift=text, Start=datetime, End=datetime], {{Table.SelectColumns(Table.FirstN(_, 1), {"Shift"}){0}[Shift], List.Min([Start]), List.Max([End]) }}), type table }},
        GroupKind.Local,
        (x,y)=> Number.From( (Time.From(x[End]) <> #time(23,59,59) and Time.From(y[Start]) <> #time(0,0,0)) or Duration.TotalSeconds(y[Start]-x[End]) > 1 ) ),
    Merged = Table.Combine(GroupedRowsLocal[Merged])
in
    Merged

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

ronrsnfld
Super User
Super User

Here is a modification of my last query, using the same algorithm but expanded to allow for workspans of 24 hours or longer.

 

It uses a separate function to calculate the shift times adjusted to account for the dates involved.

It also uses Parameters to be the source for the shift times instead of hard-coding them.

The "ShiftNames" list is hard-coded within the separate function.

 

The Parameters are named "Day", "Maintenance" and "Night" and contain the Start Time for each shift (in this case 7:00:00, 17:00:00, 21:00)

ronrsnfld_0-1720781945044.png

 

Function (name: fnShiftDateTimes)

 

(dttStart as datetime, dttEnd as datetime)=>

let 
    days = Number.RoundAwayFromZero(Duration.TotalDays(dttEnd-dttStart))+2,
    S0 = Date.From(dttStart) & #time(0,0,0),
    shiftList = List.Generate(
        ()=>[S1=Date.From(dttStart) & Day,
             S2=Date.From(dttStart) & Maintenance,
             S3=Date.From(dttStart) & Night,
             idx=0],
        each [idx] < days,
        each [S1=Date.AddDays(Date.From(dttStart) & Day,[idx]+1),
             S2=Date.AddDays(Date.From(dttStart) & Maintenance,[idx]+1),
             S3=Date.AddDays(Date.From(dttStart) & Night,[idx]+1),
             idx =[idx]+1],
        each {[S1],[S2],[S3]}),
     shiftNames = {{"Night"} & List.Repeat({"Day","Maintenance","Night"},days)}

in 
    {{S0} & List.Combine(shiftList)} & shiftNames

 

 

 Main Query

 

let
   Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcAgDETRViLOkRhvWdwKov82YgJWcv16DNBasUpUGawbqZs6rOwr6sbiRCP1vRWpCcnJnCWgVB6OECjdke4YDAj2pmCXK0aZTCeLS4B0thqvtgaRi/wtyu94unzh7TjjNxP+4uVylt4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
   #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"State Start Time", type nullable datetime}, {"State End Time", type nullable datetime}}),

    #"Add Shifts" = Table.AddColumn(#"Changed Type","Shift List",(r)=>
        let

//Generate a list of adjusted shift date times (ast) and the corresponding shifts (sh) 
//to be used in the List.Generate function
            sdd = List.Buffer(fnShiftDateTimes(r[State Start Time], r[State End Time])),
            sh = sdd{1},
            ast = sdd{0},

            splits = List.Generate(
                ()=> [S=if r[State Start Time] < ast{1} and r[State End Time] > ast{0} 
                                then List.Max({ast{0},r[State Start Time]}) else null,
                        E = if r[State End Time] > ast{0} and r[State Start Time] < ast{1}
                                then List.Min({ast{1}, r[State End Time]}) else null,
                        sft = sh{0},
                        idx=0],
                each [idx] < (List.Count(ast) -1),
                each [S=if r[State Start Time] < ast{[idx]+2} and r[State End Time] > ast{[idx]+1} 
                                then List.Max({ast{[idx]+1},r[State Start Time]}) else null,
                        E = if r[State End Time] > ast{[idx]+1} and r[State Start Time] < ast{[idx]+2}
                                then List.Min({ast{[idx]+2}, r[State End Time]}) else null,
                        sft = sh{[idx]+1},
                        idx=[idx]+1],
                each Record.FromList({[S],[E],[sft]},{"Start","End","Shift"}))

        in 
            List.Select(splits, each Record.Field(_,"Start") <> null),
            type {[Start=datetime, End=datetime, Shift=text]}
    ),
    #"Expanded Shift List" = Table.ExpandListColumn(#"Add Shifts", "Shift List"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Shift List",{"State Start Time", "State End Time"}),
    #"Expanded Shift List1" = Table.ExpandRecordColumn(#"Removed Columns", "Shift List",  {"Start", "End", "Shift"})
in
    #"Expanded Shift List1"

 

 

 

Source (courtesy of @dufoq3 )

ronrsnfld_1-1720782036872.png

Results

ronrsnfld_2-1720782103322.png

 

See also my shifts.pbix file, if you like.

 

 

ronrsnfld
Super User
Super User

You wrote the query I provided took a fairly long time on your data set.

I provide a very different algorithm that should execute considerably faster. On a small data sample, it executes in 1/100 of the time, giving the same results as my original sample. Although the End time is indicated as the start of the next shift. But that one second difference can easily be added in if that makes a difference in your desired output.

Note that no other queries are required -- this is stand-alone

Please give it a try:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3dCxDcAgDETRVSJqJPDhAGEVxP5rxGXia1DKtF+vOHvOoEnOhAw96lCE+Aht5BZWnD4509l0Z0TIWHIGbPweqWzqF8ObhTZvGPBd8HdtGd4Mv7lcZCy9jWYyln5g7IfrBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"State Start Time", type nullable datetime}, {"State End Time", type nullable datetime}}),

    #"Add Shifts" = Table.AddColumn(#"Changed Type","Shift List",(r)=>
        let
            n0S = Date.From(r[State Start Time]) & #time(0,0,0), 
            d1S = Date.From(r[State Start Time]) & #time(7,0,0),    
            m1S = Date.From(r[State Start Time]) & #time(17,0,0),
            n1S = Date.From(r[State Start Time]) & #time(21,0,0),
            d2S = Date.AddDays(Date.From(r[State Start Time]) & #time(7,0,0),1),
            m2S = Date.AddDays(Date.From(r[State Start Time]) & #time(17,0,0),1),
            n2S = Date.AddDays(Date.From(r[State Start Time]) & #time(21,0,0),1),
            sh = {"Night","Day","Maintenance","Night","Day","Maintenance","Night"},

        //adjusted shift times
            ast = {n0S, d1S, m1S, n1S,d2S, m2S,n2S},

            splits = List.Generate(
                ()=> [S=if r[State Start Time] < ast{1} and r[State End Time] > ast{0} 
                                then List.Max({ast{0},r[State Start Time]}) else null,
                        E = if r[State End Time] > ast{0} and r[State Start Time] < ast{1}
                                then List.Min({ast{1}, r[State End Time]}) else null,
                        sft = sh{0},
                        idx=0],
                each [idx] < (List.Count(ast) -1),
                each [S=if r[State Start Time] < ast{[idx]+2} and r[State End Time] > ast{[idx]+1} 
                                then List.Max({ast{[idx]+1},r[State Start Time]}) else null,
                        E = if r[State End Time] > ast{[idx]+1} and r[State Start Time] < ast{[idx]+2}
                                then List.Min({ast{[idx]+2}, r[State End Time]}) else null,
                        sft = sh{[idx]+1},
                        idx=[idx]+1],
                each Record.FromList({[S],[E],[sft]},{"Start","End","Shift"}))

        in 
            List.Select(splits, each Record.Field(_,"Start") <> null),
            type {[Start=datetime, End=datetime, Shift=text]}
    ),
    #"Expanded Shift List" = Table.ExpandListColumn(#"Add Shifts", "Shift List"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Shift List",{"State Start Time", "State End Time"}),
    #"Expanded Shift List1" = Table.ExpandRecordColumn(#"Removed Columns", "Shift List",  {"Start", "End", "Shift"})
in
    #"Expanded Shift List1"

 

 

@mmurdock2020 Please note that, as mentioned by @dufoq3 in a PM to me, my latest solution will not work if your working time spans 24 hours or more.

If that should be an issue, the fix is relatively simple and I will post it later today, along with a more flexible method to enter Shift Times.

dufoq3
Super User
Super User

Hi @mmurdock2020, another solution

 

@ronrsnfld used times with seconds, but I decided to consider only hours and make some if statements to solve this task. This query should be faster, but it is up to you to test it. I spent few hours to make this query, but it was good exercise.

 

Before

dufoq3_4-1720535802341.png

 

After

dufoq3_5-1720535815209.png

 

You can edit shifts here:

dufoq3_3-1720531104881.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcAgDETRViLOkRhvWdwKov82YgJWcv16DNBasUpUGawbqZs6rOwr6sbiRCP1vRWpCcnJnCWgVB6OECjdke4YDAj2pmCXK0aZTCeLS4B0thqvtgaRi/wtyu94unzh7TjjNxP+4uVylt4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
    _Shifts = #table(type table[Shift=text, Start=time, End=time],
        { { "Night",       #time( 0,0,0), #time( 6,59,59) },
          { "Day",         #time( 7,0,0), #time(16,59,59) },
          { "Maintenance", #time(17,0,0), #time(20,59,59) },
          { "Night",       #time(21,0,0), #time(23,59,59) } }),
    Ad_Hours = Table.AddColumn(_Shifts, "Hours", each 
        [ start = Time.StartOfHour([Start]),
          end = Time.EndOfHour([End]),
          a = List.Times(start, Duration.TotalHours(end-start), #duration(0,1,0,0))
        ][a], type list),
    ExpandedHours = Table.ExpandListColumn(Ad_Hours, "Hours"),
    TransformHours = Table.TransformColumns(ExpandedHours, {{"Hours", Time.Hour, Int64.Type}}),
    StepBack = Source,
    ChangedType_US = Table.TransformColumnTypes(StepBack,{{"State Start Time", type datetime}, {"State End Time", type datetime}}, "en-US"),
    AddedIndex = Table.AddIndexColumn(ChangedType_US, "Index", 0, 1, Int64.Type),
    Ad_HourStart = Table.AddColumn(AddedIndex, "HourStart", each Time.Hour([State Start Time]), Int64.Type),
    Ad_HourEnd = Table.AddColumn(Ad_HourStart, "HourEnd", each Time.Hour([State End Time]), Int64.Type),
    Ad_Datetimes = Table.AddColumn(Ad_HourEnd, "DateTimes", each 
        [ start = Date.From([State Start Time]) & #time([HourStart], 0, 0),
          end = Date.From([State End Time]) & #time([HourEnd], 0, 0),
          a = List.DateTimes(start, Duration.TotalHours(end-start)+1, #duration(0,1,0,0)),
          b = try {1} & List.Repeat({0}, List.Count(a)-2) & {1} otherwise {1},
          c = Table.FromColumns({a, b}, type table[DateTimes=datetime, StartEnd=Int64.Type])
        ][c], type table ),
    ExpandedDateTimes = Table.ExpandTableColumn(Ad_Datetimes, "DateTimes", {"DateTimes", "StartEnd"}),
    Ad_WorkedHours = Table.AddColumn(ExpandedDateTimes, "WorkedHours", each Time.Hour([DateTimes])),
    MergedQueriesByHours = Table.NestedJoin(Ad_WorkedHours, {"WorkedHours"}, TransformHours, {"Hours"}, "Shifts", JoinKind.LeftOuter),
    ExpandedShifts = Table.ExpandTableColumn(MergedQueriesByHours, "Shifts", {"Shift", "Start", "End"}, {"Shift", "Shift.Start", "Shift.End"}),
    Ad_WorkedDate = Table.AddColumn(ExpandedShifts, "WorkedDate", each DateTime.Date([DateTimes]), type date),
    Ad_StateStartDate = Table.AddColumn(Ad_WorkedDate, "State Start Date", each DateTime.Date([State Start Time]), type date),
    Ad_StateEndDate = Table.AddColumn(Ad_StateStartDate, "State End Date", each DateTime.Date([State End Time]), type date),
    fn_Transform = 
      (myTable as table)=>
      [
        // _Detail = GroupedRowsGlobal{[Index=1]}[All],
        _Detail = myTable,
        _Sort = Table.Sort(_Detail, {{"DateTimes", Order.Ascending}}),
        _Buffered = Table.Buffer(_Sort),
        _GroupedRows = Table.Group(_Buffered, {"Shift"}, {{"All", each _, type table},
            { "Start", each if List.First([StartEnd]) = 1 and List.First([WorkedDate]) = List.First([State Start Date]) then List.Max({ List.First([State Start Time]), List.First([WorkedDate]) & List.First([Shift.Start]) }) else List.First([WorkedDate]) & List.First([Shift.Start]), type datetime},
            { "End", each if List.Last([StartEnd]) = 1 and List.Last([WorkedDate]) = List.Last([State End Date]) then List.Min({ List.Last([State End Time]), List.Last([WorkedDate]) & List.Last([Shift.End]) }) else List.Last([WorkedDate]) & List.Last([Shift.End]), type datetime}  }, GroupKind.Local),
        _RemovedColumns = Table.RemoveColumns(_GroupedRows,{"All"})
      ][_RemovedColumns],
    GroupedRowsGlobal = Table.Group(Ad_StateEndDate, {"Index"}, {{"All", each _, type table}, {"Transformed", fn_Transform, type table}}),
    CombinedTransformed = Table.Combine(GroupedRowsGlobal[Transformed])
in
    CombinedTransformed

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thank you very much for the work you put into this! The solution I previously accepted actually is not the most effecient when calculating for my data. As you noted, since it calculates by the second it takes a very long time. With my data to import from Power Query it takes approximately 14 hours. I see the potential your code has for improving the issue however I seem to be getting a list error, List.Count = -1 after importing my data. This error appears the column which is supposed to comprise of a list which later becomes the new column start and end time data. Any ideas on how to fix this?

Hi, check your data whether you have filled each row for [State Start Time] and [State End Time] with datetime values. Could you provide some new sample data which simulates the error?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

I have ensured the datetime values are correct and to my knowledge I do not believe this is the error. Here is a set of data I am working with:67yj6j67j7j.png

I've repaired code above. Try it now and let me know.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

ronrsnfld
Super User
Super User

Given this data:

ronrsnfld_0-1720124076449.png

If you want this for results:

ronrsnfld_2-1720124140436.png

 

See my Shifts.pbix file.

Note it includes three queries

 

Shift Times

With Source Data set up a bit different from your example: 

ronrsnfld_3-1720124674871.png

 

 

let
    Source = Table.FromColumns({
        {#time(0,0,0),#time(7,0,0),#time(17,0,0),#time(21,0,0)},
        {#time(6,59,59), #time(16,59,59),#time(20,59,59),#time(23,59,59)},
        {"Night","Day","Maintenance","Night"}},
        type table[State Start Time=time, State End Time=time, Shift=text]),

//Add list of times by the second
//Times List needs to cover two days to account for shifts overlapping days
#"Times List" = Table.AddColumn(Source,"Times", (r)=> 
                    List.Sort(
                        List.Combine(
                            List.TransformMany(
                                List.DateTimes(
                                DateTime.From(r[State Start Time]), 
                                Duration.TotalSeconds(r[State End Time]-r[State Start Time]) + 1,
                                #duration(0,0,0,1)),
                            each {Date.AddDays(_,1)},
                            (x,y)=> {x,y})), Order.Ascending)                        
                            ,type {datetime}),
    #"Removed Columns" = Table.RemoveColumns(#"Times List",{"State Start Time", "State End Time"}),
    #"Expanded Times" = Table.ExpandListColumn(#"Removed Columns", "Times")
in
    #"Expanded Times"

 

 

fnShiftDateTimes

 

(startDt as date)=>

   Table.TransformColumns(
      ShiftTimes, 
      {"Times", 
         each DateTime.From(Number.From(startDt) + Number.From(_)),
      type datetime})

 

 

Shifts

Main Query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcrLCQAhDAXAViRnIXn5yJJWxP7bMC56HWZOCgZYRb3BMzwlqF/0ppbAodUnGb+IRKRaRWM9D1LpvfHeSCmXaj9V+66stQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"State Start Time" = _t, #"State End Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"State Start Time", type nullable datetime}, {"State End Time", type nullable datetime}}),
    #"Add allTimes" = Table.AddColumn(#"Changed Type", "allTimes", each 
        List.DateTimes([State Start Time], Duration.TotalSeconds([State End Time]-[State Start Time])+1,#duration(0,0,0,1)),
            type {datetime}),
    #"Removed Columns" = Table.RemoveColumns(#"Add allTimes",{"State Start Time", "State End Time"}),
    #"Add Shifts" = Table.AddColumn(#"Removed Columns","Shifts", (r)=>
        let
            #"Expanded allTimes" = Table.Sort(Table.FromList(r[allTimes],Splitter.SplitByNothing(),{"allTimes"}),"allTimes"),
            #"Shift Times" = fnShiftDateTimes(Date.From(#"Expanded allTimes"[allTimes]{0})),
            #"Join" = Table.NestedJoin(#"Expanded allTimes","allTimes", #"Shift Times","Times","Joined",JoinKind.LeftOuter),
            #"Extract Shift" = Table.ExpandTableColumn(Join, "Joined", {"Shift"}, {"Shift"}),
            #"Grouped Rows" = Table.Group(#"Extract Shift", {"Shift"}, {
                {"Shift Start Time", each List.Min([allTimes]), type nullable datetime}, 
                {"Shift End Time", each List.Max([allTimes]), type nullable datetime}},
                GroupKind.Local)
        in 
            #"Grouped Rows"
        ),
    #"Removed Columns1" = Table.RemoveColumns(#"Add Shifts",{"allTimes"}),
    #"Expanded Shifts" = Table.ExpandTableColumn(#"Removed Columns1", "Shifts", {"Shift", "Shift Start Time", "Shift End Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Shifts",{{"Shift", type text}, {"Shift End Time", type datetime}})
in
    #"Changed Type1"

 

 

 

 

This was exactly what I was looking for! Thank you so much!! You are a Power BI wizzard  🧙

Syk
Super User
Super User

Not sure if this will help but you may want to use a time dimension table and create a relationship to it.
Here's the article that explains it.
I did add a column for you to determine which shift you're in if you want to use this script:

let
    Source = List.Times(#time(0,0,0),24*60*60,#duration(0,0,0,1)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),
    #"Inserted Hour" = Table.AddColumn(#"Changed Type", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Time]), Int64.Type),
    #"Inserted Second" = Table.AddColumn(#"Inserted Minute", "Second", each Time.Second([Time]), type number),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Second", "AM/PM", each if [Hour] < 12 then "a.m." else "p.m."),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"AM/PM", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Time", "Time - Copy"),
    #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"Time - Copy"}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns", "Hour", "Hour - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column1",{{"Hour - Copy", "Hour label"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Hour label", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Hour label"}),
    #"Inserted Prefix" = Table.AddColumn(#"Removed Columns1", "Prefix", each "0" & Text.From([Hour], "en-NZ"), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Prefix",{{"Prefix", "Hour Label"}}),
    #"Extracted Last Characters" = Table.TransformColumns(#"Renamed Columns2", {{"Hour Label", each Text.End(_, 2), type text}}),
    #"Inserted Prefix1" = Table.AddColumn(#"Extracted Last Characters", "Prefix", each "0" & Text.From([Minute], "en-NZ"), type text),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Prefix1",{{"Prefix", "Minute Label"}}),
    #"Extracted Last Characters1" = Table.TransformColumns(#"Renamed Columns3", {{"Minute Label", each Text.End(_, 2), type text}}),
    #"Inserted Prefix2" = Table.AddColumn(#"Extracted Last Characters1", "Prefix", each "0" & Text.From([Second], "en-NZ"), type text),
    #"Renamed Columns4" = Table.RenameColumns(#"Inserted Prefix2",{{"Prefix", "Second Label"}}),
    #"Extracted Last Characters2" = Table.TransformColumns(#"Renamed Columns4", {{"Second Label", each Text.End(_, 2), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Last Characters2", "Index", 0, 1),
    #"Renamed Columns5" = Table.RenameColumns(#"Added Index",{{"Index", "TimeKey"}}),
    #"Inserted Modulo" = Table.AddColumn(#"Renamed Columns5", "Modulo", each Number.Mod([Hour], 12), type number),
    #"Renamed Columns6" = Table.RenameColumns(#"Inserted Modulo",{{"Modulo", "Hour Bin 12"}}),
    #"Inserted Integer-Division" = Table.AddColumn(#"Renamed Columns6", "Integer-Division", each Number.IntegerDivide([Hour], 8), Int64.Type),
    #"Renamed Columns7" = Table.RenameColumns(#"Inserted Integer-Division",{{"Integer-Division", "Hour Bin 8"}}),
    #"Inserted Integer-Division1" = Table.AddColumn(#"Renamed Columns7", "Integer-Division", each Number.IntegerDivide([Hour], 6), Int64.Type),
    #"Renamed Columns8" = Table.RenameColumns(#"Inserted Integer-Division1",{{"Integer-Division", "Hour Bin 6"}}),
    #"Inserted Integer-Division2" = Table.AddColumn(#"Renamed Columns8", "Integer-Division", each Number.IntegerDivide([Hour], 4), Int64.Type),
    #"Renamed Columns9" = Table.RenameColumns(#"Inserted Integer-Division2",{{"Integer-Division", "Hour Bin 4"}}),
    #"Inserted Integer-Division3" = Table.AddColumn(#"Renamed Columns9", "Integer-Division", each Number.IntegerDivide([Hour], 3), Int64.Type),
    #"Renamed Columns10" = Table.RenameColumns(#"Inserted Integer-Division3",{{"Integer-Division", "Hour Bin 3"}}),
    #"Inserted Integer-Division4" = Table.AddColumn(#"Renamed Columns10", "Integer-Division", each Number.IntegerDivide([Hour], 2), Int64.Type),
    #"Renamed Columns11" = Table.RenameColumns(#"Inserted Integer-Division4",{{"Integer-Division", "Hour Bin 2"}}),
    #"Inserted Integer-Division5" = Table.AddColumn(#"Renamed Columns11", "Integer-Division", each Number.IntegerDivide([Minute], 30), Int64.Type),
    #"Multiplied Column" = Table.TransformColumns(#"Inserted Integer-Division5", {{"Integer-Division", each _ * 30, type number}}),
    #"Renamed Columns12" = Table.RenameColumns(#"Multiplied Column",{{"Integer-Division", "Minute Bin 30"}}),
    #"Inserted Integer-Division6" = Table.AddColumn(#"Renamed Columns12", "Integer-Division", each Number.IntegerDivide([Minute], 15), Int64.Type),
    #"Multiplied Column1" = Table.TransformColumns(#"Inserted Integer-Division6", {{"Integer-Division", each _ * 15, type number}}),
    #"Renamed Columns13" = Table.RenameColumns(#"Multiplied Column1",{{"Integer-Division", "Minute Bin 15"}}),
    #"Inserted Integer-Division7" = Table.AddColumn(#"Renamed Columns13", "Integer-Division", each Number.IntegerDivide([Minute], 10), Int64.Type),
    #"Multiplied Column2" = Table.TransformColumns(#"Inserted Integer-Division7", {{"Integer-Division", each _ * 10, type number}}),
    #"Renamed Columns14" = Table.RenameColumns(#"Multiplied Column2",{{"Integer-Division", "Minute Bin 10"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns14", "Shift", each if [Time] >= #time(17, 0, 0) and [Time] < #time(21, 0, 0) then "Maintenance" else if [Time] >= #time(7, 0, 0) and [Time] < #time(17, 0, 0) then "Day" else if [Time] >= #time(21, 0, 0) or [Time] < #time(7, 0, 0) then "Night"
else "Unknown")
in
    #"Added Custom1"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors