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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

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