Duration of alarms and between

[Lucbft]

Hello,

I have a list of event sorted by datetime like this one :

datetime                / location / sensor / ID_Event / Status / Index / linked_index

20231108_083024 ; garage    ; cam1   ; EVT01      ; ON     ;10001

20231108_083122 ; kitchen    ; cam1   ; EVT05      ; ON    ;10002

20231108_083126 ; kitchen    ; cam2   ; EVT03      ; ON    ;10003

20231108_083340 ; garage    ; cam2   ; EVT01      ; ON    ;10004

20231108_083624 ; garage    ; cam3   ; EVT01      ; ON    ;10005

20231108_083724 ; garage    ; cam1   ; EVT01      ; OFF   ;10006 ; ??? > 10001

20231108_083824 ; garage    ; cam1   ; EVT03      ; OFF   ;10007 ; ??? > 10005

20231108_083126 ; kitchen    ; cam2   ; EVT03      ; OFF   ;10008 ; ??? > 10003

20231108_084024 ; garage    ; cam1   ; EVT01      ; ON    ;10009 ; ??? > 10006

I 'd like to add a column "linked index" to get for each record, the index of the previous occurence, so with the same Location, sensor and ID_event.

After linking the indexes, I'll calculate duration betwen status On and OFF (linked_index 10001) , and also between OFF and On ( linked_index 10006).

I try with table.max but not properly.

I appreciate a query quite fast (so searching before the current index instead of all the list).

Thank's for you help,

[Lucbft · Accepted solution]

thank's AlienSX for your answer.

let
    Source = events_table,
    dt = Table.TransformColumns(Source, {"datetime", each DateTime.FromText(_, [Format = "yyyyMMdd_hhmmss"])}),
    f = (tbl as table) =>
        [rows = List.Buffer(Table.ToList(tbl, (x) => x)),
        gen = List.Generate(
            () => [i = 0, row = rows{i} & {null, null}],
            (x) => rows{x[i]}? <> null,
            (x) => [i = x[i] + 1, row = rows{i} & {x[row]{5}, rows{i}{0} - x[row]{0}}],
            (x) => x[row]
        )][gen],
    g = Table.Group(dt, {"location", "sensor", "ID_Event"}, {{"events", each f(Table.Sort(_, {"datetime", {"Status", Order.Descending}} ))}}),
    z = Table.FromList(List.Combine(g[events]), (x) => x, Table.ColumnNames(Source) & {"linked_index", "duration"})
in
    z

this code is working properly but : Is there a simpler way to get linked_index only ?

I ask this because the function f() is a litlle bit hard to upgrade if my columns number change.

Also please ignore date format to the column "datetime" and do not calculate durations. I'll do it after.

thank's !

[AlienSx · Accepted solution]

@Lucbft then better simply shift index column as @ronrsnfld did.

let
    Source = events_table,
    gr_columns = {"location", "sensor", "ID_Event"},
    columns = Table.ColumnNames(Source) & {"linked_index"},
    exp_columns = List.Difference(columns, gr_columns),
    gr = 
        Table.Group(
            Source, gr_columns, 
            {{"tbl", each 
                Table.FromColumns(
                    Table.ToColumns(Table.Sort(_, {"datetime", {"Status", Order.Descending}})) & 
                    {{null} & List.RemoveLastN(_[Index], 1)}, 
                    columns
                )
            }}),
    expand = Table.ExpandTableColumn(gr, "tbl", exp_columns)
in
    expand

[ronrsnfld · Accepted solution]

If, as you wrote, you really want only the linked_index column to be added, then some minor changes in the code I previously supplied will do that:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"datetime", type text}, {"location", type text}, {"sensor", type text}, 
        {"ID_Event", type text}, {"Status", type text}, {"Index", Int64.Type}}),

//Add index column to be able to resort back to original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Order", 0, 1, Int64.Type),
    
    #"Grouped Rows" = Table.Group(#"Added Index", {"location", "sensor", "ID_Event"}, {
    
    //Add shifted Index column to return linked_index
        {"Shifted", each Table.FromColumns(
            Table.ToColumns(_) 
             & {{null} & List.RemoveLastN([Index],1)},
             Table.ColumnNames(_) & {"linked_index"}), 
        
        type table [datetime=text, location=nullable text, 
        sensor=nullable text, ID_Event=nullable text, Status=nullable text, 
        Index=nullable number, Order=number, linked_index=number]}}),

    #"Expanded Shifted" = Table.ExpandTableColumn(#"Grouped Rows", 
        "Shifted", {"datetime", "Status", "Index", "Order", "linked_index"}),

//return rows to original order
    #"Sorted Rows" = Table.Sort(#"Expanded Shifted",{{"Order", Order.Ascending}}),

//delete extra columns and return columns to original order
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Order"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",
        {"datetime", "location", "sensor", "ID_Event", "Status", "Index","linked_index"})
in
    #"Reordered Columns"

from your original data => 

Note that line 7 here has no match as your original data does not have a linked_index for that item

[AlienSx]

Hello, @Lucbft 

let
    Source = events_table,
    dt = Table.TransformColumns(Source, {"datetime", each DateTime.FromText(_, [Format = "yyyyMMdd_hhmmss"])}),
    f = (tbl as table) =>
        [rows = List.Buffer(Table.ToList(tbl, (x) => x)),
        gen = List.Generate(
            () => [i = 0, row = rows{i} & {null, null}],
            (x) => rows{x[i]}? <> null,
            (x) => [i = x[i] + 1, row = rows{i} & {x[row]{5}, rows{i}{0} - x[row]{0}}],
            (x) => x[row]
        )][gen],
    g = Table.Group(dt, {"location", "sensor", "ID_Event"}, {{"events", each f(Table.Sort(_, {"datetime", {"Status", Order.Descending}} ))}}),
    z = Table.FromList(List.Combine(g[events]), (x) => x, Table.ColumnNames(Source) & {"linked_index", "duration"})
in
    z

[Lucbft · Accepted solution]

thank's AlienSX for your answer.

let
    Source = events_table,
    dt = Table.TransformColumns(Source, {"datetime", each DateTime.FromText(_, [Format = "yyyyMMdd_hhmmss"])}),
    f = (tbl as table) =>
        [rows = List.Buffer(Table.ToList(tbl, (x) => x)),
        gen = List.Generate(
            () => [i = 0, row = rows{i} & {null, null}],
            (x) => rows{x[i]}? <> null,
            (x) => [i = x[i] + 1, row = rows{i} & {x[row]{5}, rows{i}{0} - x[row]{0}}],
            (x) => x[row]
        )][gen],
    g = Table.Group(dt, {"location", "sensor", "ID_Event"}, {{"events", each f(Table.Sort(_, {"datetime", {"Status", Order.Descending}} ))}}),
    z = Table.FromList(List.Combine(g[events]), (x) => x, Table.ColumnNames(Source) & {"linked_index", "duration"})
in
    z

this code is working properly but : Is there a simpler way to get linked_index only ?

I ask this because the function f() is a litlle bit hard to upgrade if my columns number change.

Also please ignore date format to the column "datetime" and do not calculate durations. I'll do it after.

thank's !

[AlienSx · Accepted solution]

@Lucbft then better simply shift index column as @ronrsnfld did.

let
    Source = events_table,
    gr_columns = {"location", "sensor", "ID_Event"},
    columns = Table.ColumnNames(Source) & {"linked_index"},
    exp_columns = List.Difference(columns, gr_columns),
    gr = 
        Table.Group(
            Source, gr_columns, 
            {{"tbl", each 
                Table.FromColumns(
                    Table.ToColumns(Table.Sort(_, {"datetime", {"Status", Order.Descending}})) & 
                    {{null} & List.RemoveLastN(_[Index], 1)}, 
                    columns
                )
            }}),
    expand = Table.ExpandTableColumn(gr, "tbl", exp_columns)
in
    expand

[ronrsnfld]

Kudos for the Format option of the DateTime.FromText function. But what about globalization? Setting different cultures (that don't use "y" for "year", for example) doesn't seem to have any effect. Or maybe I'm looking at incorrectly.

[AlienSx]

@ronrsnfld "yyyy" is universal 4 digits of year, across all cultures. Culture comes in to play when you are dealing with text representation of month (February, Fevrier etc.).   

[Lucbft]

Thank's AlienSX and Ron for your help !!!

[ronrsnfld]

Thanks. It seems that the "MDY" representations in PQ are different than the representations in MS Excel. Good to know.

[AlienSx]

@ronrsnfld DateTime.FromText web page has a link to format strings we may use in it. 

[ronrsnfld]

I did see that, but, because of exposure to the different format strings used for different countries in other MS products, found it unclear, hence my question here.

[ronrsnfld]

Seems to me that this may be an XY Problem.

If what you want is to calculate the duration between each event, just calculate that directly.

If you really need the index of the previous item for some other purpose, you can use the same algorithm to extract it.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"datetime", type text}, {"location", type text}, {"sensor", type text}, 
        {"ID_Event", type text}, {"Status", type text}, {"Index", Int64.Type}}),

//change datetime column to actual datetime
    #"DateTime" = Table.TransformColumns(#"Changed Type",
        {"datetime", each 
            let 
                split = Text.Split(_,"_"),
                dt = Date.From(Number.ToText(Number.From(split{0}),"0000-00-00")),
                tm = Time.From(Number.ToText(Number.From(split{1}),"00:00:00"))
            in dt & tm, type datetime}),

//Add index column to be able to resort back to original order
    #"Added Index" = Table.AddIndexColumn(DateTime, "Order", 0, 1, Int64.Type),
    
    #"Grouped Rows" = Table.Group(#"Added Index", {"location", "sensor", "ID_Event"}, {
    
    //Add shifted datetime column to calculate duration
        {"Shifted", each Table.FromColumns(
            Table.ToColumns(_) 
             & {{null} & List.RemoveLastN([datetime],1)},
             Table.ColumnNames(_) & {"Shifted DateTime"}), 
        
        type table [datetime=datetime, location=nullable text, 
        sensor=nullable text, ID_Event=nullable text, Status=nullable text, 
        Index=nullable number, Order=number, Shifted DateTime=datetime]}}),

    #"Expanded Shifted" = Table.ExpandTableColumn(#"Grouped Rows", 
        "Shifted", {"datetime", "Status", "Index", "Order", "Shifted DateTime"}),

//calculate the duration
    #"Added Custom" = Table.AddColumn(#"Expanded Shifted", "Duration", 
        each [datetime]-[Shifted DateTime], type duration),

//return rows to original order
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Order", Order.Ascending}}),

//delete extra columns and return columns to original order
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Order", "Shifted DateTime"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",
        {"datetime", "location", "sensor", "ID_Event", "Status", "Index", "Duration"})
in
    #"Reordered Columns"

Results from your data

[Lucbft]

No one has a solution for me ???