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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Anonymous,

 

here is similar approach. I've changed source data a bit.

No need to sort.

 

Result:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc/NCsIwEATgVwk5F5rd/Gi8CT31FUpPItiLvRTBt7fai5mFdnPcYSYfGQbbz4+n6ea7bez1tkyvaXkbWg9uY8uOgzlfnCtv7+zYqJa+WObvS6plBjOrzQwmOTW6VctAy27V/4D0LqFLepfQZb3L6LLeZXT9nsvrQdCFZEcWaw/rcPjnUHYhOfo1dIskVthR2LHCjsJOFXYSdqqwk7BPP3v8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, #"type" = _t, #"shift start date time" = _t, #"shift end date time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"shift start date time", type datetime}, {"shift end date time", type datetime}}),
    Ad_ShiftStartDate = Table.AddColumn(#"Changed Type", "shift start date", each DateTime.Date([shift start date time]), type date),
    #"Grouped Rows" = Table.Group(Ad_ShiftStartDate, {"name", "type", "shift start date"}, {{"shift start date time", each List.Min([shift start date time]), type nullable datetime}, {"shift end date time", each List.Max([shift end date time]), type nullable datetime}, {"Detail", each _, type table}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"shift start date"})
in
    #"Removed Columns"