Lists of Groups by overlapping datetime

[Memo-mohammed]

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/2023 11:47	29/8/2023 11:48
592	27020		29/8/2023 11:19	29/8/2023 11:23
592	27020		29/8/2023 11:07	29/8/2023 11:14
592	2312	2	29/8/2023 10:45	29/8/2023 10:48
592	2312	1	29/8/2023 10:46	29/8/2023 10:48
592	2312	0	29/8/2023 10:46	29/8/2023 10:48
592	2312	2	29/8/2023 11:30	29/8/2023 11:35
592	2312	1	29/8/2023 11:30	29/8/2023 11:35
592	2312	0	29/8/2023 11:30	29/8/2023 11:35
592	2312	1	29/8/2023 11:40	29/8/2023 11:43
592	2312	0	29/8/2023 11:40	29/8/2023 11:43
592	2312	2	29/8/2023 11:46	29/8/2023 11:49
592	2312	1	29/8/2023 11:46	29/8/2023 11:49
592	2312	0	29/8/2023 11:47	29/8/2023 11:49
592	2312	2	29/8/2023 11:55	29/8/2023 11:59
592	2312	1	29/8/2023 11:55	29/8/2023 11:59
592	2312	0	29/8/2023 11:55	29/8/2023 11:59

And I need a list of groups like this 

How can i group them by ovarlapping function in Power Query ?

Thank you

[AlienSx · Accepted solution]

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
    g2

[Memo-mohammed]

Thank you @AlienSx 

Woked perfect thanks.

Now I need Expand The list like That

- Max of Occurred-On 

- Min of Cleared-On

- New column  Sector-id with secor in Tablt (0,1) or (0.1.2) ...

thanks for your efforts 

[AlienSx]

first 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"

[Memo-mohammed]

Thank you for your replay.
What I want exactly is to assemble the [Sector-IDs] exactly like the picture (0,1,2) 

[AlienSx · Accepted solution]

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_tx

[Memo-mohammed]

worked like a charm.
Thank you @AlienSx 

[AlienSx · Accepted solution]

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
    g2