Forum Discussion
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...
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
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
grThank 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.