Forum Discussion
Solved
Requesting help to group events by time
I have about two years worth of data with several thousand IDs. I filtered this example to just show one ID but there are many 1,000. I want to group events on this table by two-hour intervals by ID....
dufoq3
Community Champion
Community ChampionHi tw2024, that means your data are not sorted as you showed as in your sample. Try this one and let me know:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddE9DoMwDAXgq6DMSPgvJnkb3St1R9z/Gg0tpYIk8xc/O/a6BuaJeBISG2iGCKIPyzOMYQnbeOUEyuDUY4cZlIdXm2eYI1YsB+e9t9E9/GBmUARXo/24jK1Q74TvnMuLXm9GLJOnLz8q9vLpfbQuq4K8xwkcG9V6ciTIsdSaDabg3Kv2T7jd2U6WueT3whPEGvfW/84v997e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"ID", type text}}, "en-US"),
GroupedRows = Table.Group(ChangedType, {"ID"}, {{"All", each [
// aDetail = GroupedRows{[ID="A"]}[All],
aDetail = Table.Sort(_, {{"Date", Order.Ascending}}),
aGroupedRows = Table.Group(aDetail, {"Date"}, {{"T", each Table.AddColumn(_, "Earliest Time", (x)=> Time.From([Date]{0}), type time), type table}}, 0,
(x,y)=> Byte.From( (y[Date] - x[Date]) >= #duration(0,2,0,0) ) ),
aCombinedT = Table.Combine(aGroupedRows[T])
][aCombinedT], type table}}),
CombinedAll = Table.Combine(GroupedRows[All])
in
CombinedAll