Forum Discussion

Memo-mohammed's avatar
Memo-mohammed
Frequent Visitor
3 years ago
Solved

Lists of Groups by overlapping datetime

Hello

Im very new to Power Query and i need help .

I have a table :

 

IDAlarm-IDSector-idOccurred-On Cleared-On
28127020 29/8/2023 12:3429/8/2023 12:39
8322312029/8/2023 11:4729/8/2023 11:48
59227020 29/8/2023 11:1929/8/2023 11:23
59227020 29/8/2023 11:0729/8/2023 11:14
5922312229/8/2023 10:4529/8/2023 10:48
5922312129/8/2023 10:4629/8/2023 10:48
5922312029/8/2023 10:4629/8/2023 10:48
5922312229/8/2023 11:3029/8/2023 11:35
5922312129/8/2023 11:3029/8/2023 11:35
5922312029/8/2023 11:3029/8/2023 11:35
5922312129/8/2023 11:4029/8/2023 11:43
5922312029/8/2023 11:4029/8/2023 11:43
5922312229/8/2023 11:4629/8/2023 11:49
5922312129/8/2023 11:4629/8/2023 11:49
5922312029/8/2023 11:4729/8/2023 11:49
5922312229/8/2023 11:5529/8/2023 11:59
5922312129/8/2023 11:5529/8/2023 11:59
5922312029/8/2023 11:5529/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

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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
  • AlienSx's avatar
    AlienSx
    3 years ago
    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

6 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
    Memo-mohammed
    Frequent Visitor
    3 years ago

    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's avatar
      AlienSx
      Icon for Super User rankSuper User
      3 years ago

      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's avatar
        Memo-mohammed
        Frequent Visitor
        3 years ago

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