Aggregate table by local minimum and local maximum

[jbetkowskawbd]

Hello, 

I have table with daily activities broken down by 30 min periods during the day.
I'd like to aggregate it - if activity lasts longer than 30 min I have a few rows - I need one row with start time as minimum from start time and end date as a max of end date. However one type of activity (A) can be separated by other type of activity (B) and in this case I would need 2 separate rows for activity A. Is it possible to achive this by transforming table in power query?

Below sample of data I have (I ave many names, many activities, many days):

name	type	shift start date time	shift end date time
John Doe	Activity 1	2/5/2024 8:00	2/5/2024 8:30
John Doe	Activity 1	2/5/2024 8:30	2/5/2024 9:00
John Doe	Activity 1	2/5/2024 9:00	2/5/2024 9:30
John Doe	Activity 1	2/5/2024 9:30	2/5/2024 10:00
John Doe	Activity 1	2/5/2024 10:00	2/5/2024 10:30
John Doe	Activity 1	2/5/2024 10:30	2/5/2024 11:00
John Doe	Activity 1	2/5/2024 11:00	2/5/2024 11:30
John Doe	Activity 1	2/5/2024 11:30	2/5/2024 12:00
John Doe	Activity 1	2/5/2024 12:00	2/5/2024 12:30
John Doe	Activity 1	2/5/2024 12:30	2/5/2024 13:00
John Doe	Activity 2	2/5/2024 13:00	2/5/2024 13:30
John Doe	Activity 2	2/5/2024 13:30	2/5/2024 14:00
John Doe	Activity 1	2/5/2024 14:00	2/5/2024 14:30
John Doe	Activity 1	2/5/2024 14:30	2/5/2024 15:00
John Doe	Activity 1	2/5/2024 15:00	2/5/2024 15:30
John Doe	Activity 1	2/5/2024 15:30	2/5/2024 16:00
John Doe	Activity 1	2/5/2024 16:00	2/5/2024 16:30
John Doe	Activity 1	2/5/2024 16:30	2/5/2024 17:00

And table I would like to achieve from this data:

name	type	shift start date time	shift end date time
John Doe	Activity 1	2/5/2024 8:00	2/5/2024 13:00
John Doe	Activity 2	2/5/2024 13:00	2/5/2024 14:00
John Doe	Activity 1	2/5/2024 14:00	2/5/2024 17:00

[dufoq3]

@jbetkowskawbd,

here is similar approach. I've changed source data a bit.

No need to sort.

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc/NCsIwEATgVwk5F5rd/Gi8CT31FUpPItiLvRTBt7fai5mFdnPcYSYfGQbbz4+n6ea7bez1tkyvaXkbWg9uY8uOgzlfnCtv7+zYqJa+WObvS6plBjOrzQwmOTW6VctAy27V/4D0LqFLepfQZb3L6LLeZXT9nsvrQdCFZEcWaw/rcPjnUHYhOfo1dIskVthR2LHCjsJOFXYSdqqwk7BPP3v8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, #"type" = _t, #"shift start date time" = _t, #"shift end date time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"shift start date time", type datetime}, {"shift end date time", type datetime}}),
    Ad_ShiftStartDate = Table.AddColumn(#"Changed Type", "shift start date", each DateTime.Date([shift start date time]), type date),
    #"Grouped Rows" = Table.Group(Ad_ShiftStartDate, {"name", "type", "shift start date"}, {{"shift start date time", each List.Min([shift start date time]), type nullable datetime}, {"shift end date time", each List.Max([shift end date time]), type nullable datetime}, {"Detail", each _, type table}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"shift start date"})
in
    #"Removed Columns"

[jbetkowskawbd]

Thank you for piece of advice, but due to reasons that I understand it doesn't work on my original data. When I copy them from PowerBI to excel and then upload from excel to PowerBI it works.
But when I go to PowerBI, open blank query and reference to original, taken from other souces, table it does not work.

[AlienSx]

@jbetkowskawbd local grouping requires mindful sorting. It goes row by row and calculates if it's time to start new group. So I'd recomment you to sort your original table by (1) name, (2) activity and (3) start date before applying my code. 

[jbetkowskawbd]

Indeed, in the meantime I also came up with this idea, after sorting it works perfectly. Thank you very much for your help. Your code is extremely clever.

[AlienSx · Accepted solution]

hello, @jbetkowskawbd 

let
    Source = your_data,
    idx = Table.AddIndexColumn(Source, "idx", 0, 1, Int64.Type),
    gr = Table.Group(
        idx, {"name", "type", "shift start date time", "idx"}, 
        {{"shift end date time", (x) => List.Last(x[#"shift end date time"])}}, GroupKind.Local,
        (s, c) => Byte.From( 
            (s[[name], [type]] <> c[[name], [type]]) or
            (Duration.TotalMinutes(c[#"shift start date time"] - s[#"shift start date time"]) <> 30 * (c[idx] - s[idx]))
            )
    )[[name], [#"type"], [#"shift start date time"], [#"shift end date time"]]
in
    gr

[jbetkowskawbd]

Thank you for your help, but after I pasted your formula into my data I received same table as source - grouping did not worked as expected. Unfortunately I don't know PowerQuery so well to fix it by myself. Stage "gr" is mysterious for me I do not know, what exactly is happening there.

[AlienSx]

@jbetkowskawbd you need to 

 - create blank query

 - open it with Advanced Editor

 - replace everything you see inside with my code

 - replace your_table variable with the name of your query. That will become a source for my code. 

Video.