Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
Feb 14, 2025
Solved

Create a Power query or Dax to find Open Status more than 1 hour

Hi Team,   I have the door table Here I want to find that whether door is being open continuously(having open status & not closed) for more than 1 hour for each DeviceId.   Data Description:...
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 14, 2025

    With Power Query that could be like

    let
        Source = Csv.Document(File.Contents("C:\Test\Door.csv"),[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        PromotHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
        SelectColumns = Table.SelectColumns(
            PromotHeaders,
            {"DeviceId", "CreatedOn", "Status"}),
        TrueToOpen = Table.ReplaceValue(SelectColumns,"true","Open",Replacer.ReplaceText,{"Status"}),
        FalseToClosed = Table.ReplaceValue(TrueToOpen,"false","Closed",Replacer.ReplaceText,{"Status"}),
        DeclareType = Table.TransformColumnTypes(
            FalseToClosed,
            {
                  {"DeviceId", Int64.Type}
                , {"CreatedOn", type datetime}
            }),
    
        #"Grouped Rows" = Table.Group(
            DeclareType, {"DeviceId"},
                {
                    {"Data", each
                     [
                        tbl = _,
    
    ////
        Buffer = Table.Buffer( Table.Sort(tbl,{{"CreatedOn", Order.Ascending}}) ),
        Created = Table.Column( Buffer, "CreatedOn" ),
        Status = Table.Column( Buffer, "Status" ),
    
        SumTime =
            List.Generate( () => [TM = 0 , nRow = 0  ],
                each [nRow] < List.Count(Created),
                each
                 [
                    TM =
                        if Status{[nRow]} = "Closed"
                        then 0
                        else  [TM] + Duration.TotalMinutes( Created{[nRow]+1} - Created{[nRow] } )  ,
                     nRow = [nRow] + 1
                  ],
                each [TM]
    
             ),
    
        ExpandTable = Table.FromColumns(
            Table.ToColumns(Buffer) & {SumTime},
            Table.ColumnNames(Buffer) & {"OpenMinutes"} ),
        MinutesType = Table.TransformColumnTypes(
            ExpandTable,
            {{"OpenMinutes", Int64.Type}})
    
                     ][MinutesType] }
    /////
                }),
    
        AddOpenInMinutes = Table.ExpandTableColumn(
            #"Grouped Rows",
            "Data",
            {"CreatedOn", "Status", "OpenMinutes"},
            {"CreatedOn", "Status", "OpenMinutes"}),
        ChangeType = Table.TransformColumnTypes(
            AddOpenInMinutes,
            {
                  {"CreatedOn", type datetime}
                , {"Status", type text}
                , {"OpenMinutes", Int64.Type}
            }),
        FixInMemory = Table.AddIndexColumn(ChangeType, "Index", 0, 1, Int64.Type),
        SortTable = Table.Sort(
            FixInMemory,
            {
                  {"DeviceId", Order.Ascending}
                , {"CreatedOn", Order.Ascending}
            }),
        RemoveIndex = Table.RemoveColumns(SortTable,{"Index"})
    in
        RemoveIndex
    • source is exported from your model to csv (attached)
    • above is done in Excel (attached)
    • On first Closed after Open we add minutes, after that reset them

Resources