Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Aggregate table by local minimum and local maximum

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 star...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    hello, Anonymous 

    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
AlienSx's avatar
AlienSx
Super User
2 years ago

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.