Forum Discussion
Solved
Lists of Groups by overlapping datetime
Hello Im very new to Power Query and i need help . I have a table : ID Alarm-ID Sector-id Occurred-On Cleared-On 281 27020 29/8/2023 12:34 29/8/2023 12:39 832 2312 0 29/8...
Hello, Memo-mohammed try this
let Source = your_table, sorted = Table.Sort(Source,{{"ID", Order.Ascending}, {"Alarm-ID", Order.Ascending}, {"Occurred-On ", Order.Ascending}, {"Cleared-On", Order.Ascending}}), g1 = Table.Group( sorted, {"ID", "Alarm-ID", "Occurred-On ", "Cleared-On"}, {{"All", each _}}, GroupKind.Local, (s, c) => Byte.From( List.AnyTrue( {s[ID] <> c[ID], s[#"Alarm-ID"] <> c[#"Alarm-ID"], s[#"Cleared-On"] < c[#"Occurred-On "]} ) )), g2 = Table.Group(g1, {"ID", "Alarm-ID"}, {{"All", each _[All]}}) in g2let Source = your_table, sorted = Table.Sort(Source,{{"ID", Order.Ascending}, {"Alarm-ID", Order.Ascending}, {"Occurred-On ", Order.Ascending}, {"Cleared-On", Order.Ascending}}), g1 = Table.Group( sorted, {"ID", "Alarm-ID", "Occurred-On ", "Cleared-On"}, {{"All", each _}}, GroupKind.Local, (s, c) => Byte.From( List.AnyTrue( {s[ID] <> c[ID], s[#"Alarm-ID"] <> c[#"Alarm-ID"], s[#"Cleared-On"] < c[#"Occurred-On "]} ) ))[All], tbl_tx = Table.Combine( List.Transform( g1, (x) => #table( {"ID", "Alarm-ID", "Max Occured-On", "Min Cleared-On", "Sector-id"}, {{x[ID]{0}, x[#"Alarm-ID"]{0}, List.Max(x[#"Occurred-On "]), List.Min(x[#"Cleared-On"]), Text.Combine(List.Transform(List.Sort(x[#"Sector-id"]), Text.From), ", ")}}) ) ) in tbl_tx
AlienSx
Super User
Super Userfirst we wrapped tables into lists, now you want to expand... I am confused, maybe this is what you want...
let
Source = your_table,
sorted = Table.Sort(Source,{{"ID", Order.Ascending}, {"Alarm-ID", Order.Ascending}, {"Occurred-On ", Order.Ascending}, {"Cleared-On", Order.Ascending}}),
g1 =
Table.Group(
sorted, {"ID", "Alarm-ID", "Occurred-On ", "Cleared-On"}, {{"All", each _}}, GroupKind.Local,
(s, c) =>
Byte.From(
List.AnyTrue(
{s[ID] <> c[ID],
s[#"Alarm-ID"] <> c[#"Alarm-ID"],
s[#"Cleared-On"] < c[#"Occurred-On "]}
)
))[All],
tbl_tx =
Table.Combine(
List.Transform(
g1,
(x) =>
#table(
{"ID", "Alarm-ID", "Max Occured-On", "Min Cleared-On", "Sector-id"},
{{x[ID]{0}, x[#"Alarm-ID"]{0}, List.Max(x[#"Occurred-On "]), List.Min(x[#"Cleared-On"]), x[#"Sector-id"]}})
)
),
#"Expanded Sector-id" = Table.ExpandListColumn(tbl_tx, "Sector-id")
in
#"Expanded Sector-id"
Thank you for your replay.
What I want exactly is to assemble the [Sector-IDs] exactly like the picture (0,1,2)
- AlienSx
let Source = your_table, sorted = Table.Sort(Source,{{"ID", Order.Ascending}, {"Alarm-ID", Order.Ascending}, {"Occurred-On ", Order.Ascending}, {"Cleared-On", Order.Ascending}}), g1 = Table.Group( sorted, {"ID", "Alarm-ID", "Occurred-On ", "Cleared-On"}, {{"All", each _}}, GroupKind.Local, (s, c) => Byte.From( List.AnyTrue( {s[ID] <> c[ID], s[#"Alarm-ID"] <> c[#"Alarm-ID"], s[#"Cleared-On"] < c[#"Occurred-On "]} ) ))[All], tbl_tx = Table.Combine( List.Transform( g1, (x) => #table( {"ID", "Alarm-ID", "Max Occured-On", "Min Cleared-On", "Sector-id"}, {{x[ID]{0}, x[#"Alarm-ID"]{0}, List.Max(x[#"Occurred-On "]), List.Min(x[#"Cleared-On"]), Text.Combine(List.Transform(List.Sort(x[#"Sector-id"]), Text.From), ", ")}}) ) ) in tbl_txworked like a charm.
Thank you AlienSx