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
Excellove15
Feb 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!
SergeiBaklan
Feb 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!
- SergeiBaklanFeb 14, 2025MVP
Could you please attached query as text file; or directly into the post using this icon
and selecting Power Query as language
- Excellove15Feb 14, 2025Iron Contributor
Hi SergeiBaklan Sir,
This is an amazing solution and wonderfully crafted!š¤©
You are truly a masterpiece and i still can't believe that this issue got resolved.
You came like a saviour for this community! If you are in linkedin please provide me your id so that i can write a short endorsement for your "Community Building" or "Power BI" skills.
Your guidance saved me hours of frustrationāI really appreciate your expertise!
I just added a calculated column on this table to give a finishing touch for alert:Just for my knowledge, Still wondering how you created below:
How did you gain this power query knowledge sir? Any blogs or books would be greatly appreciated.
Big thanks to you once again!
I will close this query and mark it as best solution.
Thanks in advance!
Happy weekend