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

[sivarajan21]

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:

The Status column shows whether door is open or Closed.

CreatedOn column shows time duration.

DeviceId column shows Deviceid.

My first requirement is:

We need to add the minutes(createdon) between each row for same deviceid where the status = open. But where a status = closed (if closed comes in between open) we reset the number of minutes.

So we sum by the previous value where status = Open -
Something like If current status = open and previous status = open then sum the time and do the below:
Sum(previous value + new sum) 

Where/if status = closed set time count = 0

Sum(previous value + new sum) is going to take into account only the consecutive open status >60 min for same divice id

whenever status is closed, this Sum(previous value + new sum) will be reset to/equal to 0

So on the first open status, the count will be 0. Then the next open status it will be time between 2 open statuses, then on the third it will be the time between two open statuses add the previous time.but any close status then time is reset.

For example of above description:

The brown color highlight does not alert because it did not complete 60 min as Closed came up.

Could you please help me with a power query logic/dax logic?

Second Requirement:

This is only a sample data. But we have billions of rows of data in our dataflow that comes from SQL views.

@marcorusso  sir came with a brilliant idea of below:
'The suggestion is not to use DAX for this calculation. You can precalculate the maximum open state duration by preprocessing your data (SQL, Power Query, Spark, anything else...). Using DAX does not make much sense because performance will quickly decrease when you try to compute the same calculation in every report through a measure.

So can you help me prepare data upfront. we need to solve the problem with a with a proper data model. for example, create a table with the longest open events upfront (in whatever technique you are comfortable with)'.

Could you please help me to achieve this?

PFA file here B&M.pbix

Please let me know if you need further info!

Thanks in advance!

 @tharunkumarRTK  @v-linyulu-msft @v-yaningy-msft 

[dufoq3]

Hi @sivarajan21, 

Two questions:

1. do you need all rows in power bi or is it enough to keep just alert rows?
2. what to you want to do with last 2 rows in this case?

[sivarajan21]

Hi @dufoq3 ,

Thanks for your quick response!

Answers to your questions:

1)Yes I need all rows in pbi

2)just keep them like other rows

thanks in advance

[dufoq3]

for number 2. Do you want to mart any of last 2 rows as alert?

[sivarajan21]

Hi @dufoq3 

Thanks for your quick response!☺

this is resolved now!

Thanks

[wini_R · Accepted solution]

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...

[sivarajan21]

Hi @wini_R ,

will get back on this