Forum Discussion
Excellove15
Feb 14, 2025Iron Contributor
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:...
- 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
SergeiBaklan
Feb 14, 2025MVP
Excellove15 , Status also could be true and false. How to process them, ignore?
- Excellove15Feb 14, 2025Iron Contributor
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!
- SergeiBaklanFeb 14, 2025MVP
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
- Excellove15Feb 14, 2025Iron Contributor
Hi SergeiBaklan ,
Many thanks for your response!
Thats amazing solution that you gave in excel within a short period of time😍
Sir, can i use this in power query code in my power bi report and what should i edit only the source?
when i use in my power bi PQ, it shows below errorPlease advise sir!
Thanks in advance!