Create a Power query logic to find Open Status more than 60 minutes

Hey sivarajan21,

This is related to your first requirement. Not sure how efficient the code is on big dataset but you can give it a try. Hopefully I haven't missed anything on the logic

let
    Source = //[your Door table]//,
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Extracted Date Time" = Table.TransformColumns(#"Promoted Headers", {{"CreatedOn", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Date Time",{{"CreatedOn", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DeviceId"}, {{"all0", each Table.AddIndexColumn(Table.Sort(_, {{"CreatedOn", Order.Ascending}}), "index0", 0, 1), type table}}),
    #"Added Custom" = Table.Combine (Table.AddColumn(#"Grouped Rows", "all1", each Table.NestedJoin([all0], {"index0"}, Table.AddIndexColumn([all0], "index1", 1, 1), {"index1"}, "all2", JoinKind.LeftOuter))[all1]),
    #"Expanded all2" = Table.ExpandTableColumn(#"Added Custom", "all2", {"DeviceId", "Status", "CreatedOn"}, {"DeviceIdPrev", "StatusPrev", "CreatedPrev"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded all2", "Duration", each if [Status] = [StatusPrev] then [CreatedOn]-[CreatedPrev] else null),
    #"Grouped with RT" = Table.Combine (Table.Group(#"Added Custom1", {"DeviceId", "Status"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"DuratRT", each fxRT("RunnTot", _, "Duration")} }, 0)[DuratRT]),
    #"Added Over1h" = Table.AddColumn(#"Grouped with RT", "Over1h", each if [DeviceId] = [DeviceIdPrev] and [StatusPrev] = "Open" and [Status] = [StatusPrev] and [RunnTot] > #duration(0,1,0,0) then "over" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Over1h",{"Morethan1hour", "index0"})
in
    #"Removed Columns"

Running total function (fxRT) used in one of the steps

( RTColumnName as text, MyTable as table, ValueColumn as text) =>
let
    Source = MyTable,
    BuffValues = List.Buffer( Table.Column( MyTable, ValueColumn ) ),
    RunningTotal = 
      List.Generate ( 
        () => [ RT = BuffValues{0}, RowIndex = 0 ],
        each  [RowIndex] < List.Count(BuffValues),
        each  [ RT = List.Sum( { [RT] , BuffValues{[RowIndex] + 1} } ),
                RowIndex = [RowIndex] + 1 ],
        each  [RT] ),
    #"Combined Table + RT" = 
      Table.FromColumns( 
        Table.ToColumns( MyTable )   
           & { Value.ReplaceType( RunningTotal, type {Int64.Type} ) } , 
        Table.ColumnNames( MyTable ) & { RTColumnName } )
in
    #"Combined Table + RT"

Pbix file: https://www.dropbox.com/scl/fi/himfr8jllkru8b8lqgt6l/B-M_over1h.pbix?rlkey=toruni4qtwtjqqeff4cpx52ja&st=xdwqusoz&dl=0