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