Forum Discussion
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:
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.
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.
So, thought not to use DAX for this calculation in future. we can precalculate the maximum open state duration by preprocessing data (SQL, Power Query(dataflow), Spark, anything else...).
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
Thanks in advance!
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
Excellove15 , Status also could be true and false. How to process them, ignore?
hI SergeiBaklan ,
Thanks! true means open and false means closed.
Apologise forget to do transform that in power query and now done!
PFA file here B&M.pbix
Thanks in advance Sir!
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
