Requesting help to group events by time

Hi webbta ,

I create sample data myself, including different IDs and different days:

Then you can put the whole M code below into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLDsMgDATQq0SsI8U/HJhduq/UfZT7X6OkpUgNYf3wePC+B+aFeBESm2iFCKJP2zPMYQvH/M8JlMFpxA4zKE+ve15hjtixVM7nbqNreGVmUAR31X5caivUB+En5/JitJsRS/P05UfHXj59VhuyKshHnMDxZlobR4LUo/ZsMAXn0bR/wu3K1ljWkt/Cjzc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"ID", type text}}),
    FillEarliestTime = (table as table) as table =>
        let
            ListOfRecords = Table.ToRecords(table),
            init = {List.First(ListOfRecords)[Date]},
            accFunc = (acc as list, curr as record) =>
                let
                    lastTime = List.Last(acc),
                    duration = Duration.TotalMinutes(curr[Date] - lastTime)
                in
                    if duration <= 120 then
                        acc & {lastTime}
                    else
                        acc & {curr[Date]},
            ListOfEarliestTimes = List.Accumulate(List.Skip(ListOfRecords, 1), init, accFunc),
            FilledTable = Table.FromColumns(Table.ToColumns(table) & {ListOfEarliestTimes}, Table.ColumnNames(table) & {"Earliest Time"})
        in
            FilledTable,
    #"Grouped Table" = Table.Group(#"Changed Type", {"ID"}, {{"GroupedTable", each FillEarliestTime(_), type table [Date=nullable datetime, ID=nullable text, Earliest Time=nullable datetime]}}),
    #"Expanded GroupedTable" = Table.ExpandTableColumn(#"Grouped Table", "GroupedTable", {"Date", "Earliest Time"}, {"Date", "Earliest Time"})
in
    #"Expanded GroupedTable"

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi tw2024, that means your data are not sorted as you showed as in your sample. Try this one and let me know:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddE9DoMwDAXgq6DMSPgvJnkb3St1R9z/Gg0tpYIk8xc/O/a6BuaJeBISG2iGCKIPyzOMYQnbeOUEyuDUY4cZlIdXm2eYI1YsB+e9t9E9/GBmUARXo/24jK1Q74TvnMuLXm9GLJOnLz8q9vLpfbQuq4K8xwkcG9V6ciTIsdSaDabg3Kv2T7jd2U6WueT3whPEGvfW/84v997e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"ID", type text}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"ID"}, {{"All", each [
            // aDetail = GroupedRows{[ID="A"]}[All],
            aDetail = Table.Sort(_, {{"Date", Order.Ascending}}),
            aGroupedRows = Table.Group(aDetail, {"Date"}, {{"T", each Table.AddColumn(_, "Earliest Time", (x)=> Time.From([Date]{0}), type time), type table}}, 0,
                (x,y)=> Byte.From( (y[Date] - x[Date]) >= #duration(0,2,0,0) ) ),
            aCombinedT = Table.Combine(aGroupedRows[T])
          ][aCombinedT], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])

in
    CombinedAll