Forum Discussion
Calculate Equipment State Duration per Shift
here a solution "complete" (corresponding to the status of the information received 😁) avoiding the use of the function List.Accumulate.
starting table:
output Table:
the code for the output table:
let ct = Table.TransformColumnTypes(Table,{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}), idx=List.RemoveLastN(List.Distinct(ct[ShiftIndex]),1), lstR=List.Generate( ()=>[r=1,pos=List.PositionOf(ct[ShiftIndex],idx{0},Occurrence.Last), lr=sfht(ct{pos},ct{pos+1})], each [r]<=List.Count(idx), each [r=[r]+1, pos=List.PositionOf(ct[ShiftIndex],idx{[r]},Occurrence.Last),lr=sfht(ct{pos},ct{pos+1}) ], each [lr] ), tc= Table.Combine({Table.FromRecords(List.Combine(lstR)),Table}), grp = Table.Group(tc, {"ShiftIndex"}, {{"shIdx", each duration(_)}}), #"Expanded shIdx" = Table.ExpandTableColumn(grp, "shIdx", {"StateDateTime", "EquipmentState", "Duration"}, {"StateDateTime", "EquipmentState", "Duration"}) in #"Expanded shIdx"the two functions used:
sfht
let listRows=(rL,rF)=> let shift=#duration(0,0,720,0), dL=rL[StateDateTime], dF=rF[StateDateTime], lr=List.Generate( ()=>[r=rL&[StateDateTime=#datetime(Date.Year(dL),Date.Month(dL),Date.Day(dL)+sh{1},sh{0},0,0)],idx=1], each [r][StateDateTime]<=dF, each [r=if Number.Mod(idx,2)=1 then [r]& [StateDateTime=[r][StateDateTime]+shift] else [r]&[ShiftIndex=[r][ShiftIndex]+1],idx=[idx]+1], each [r] ), sh=if Time.Hour(dL) < 6 then {6,0} else if Time.Hour(dL)<18 then {18,0} else {6,1} in lr in listRowsand finally duration:
let count=(tab)=> let ts=Table.Sort(tab,{"StateDateTime"}), ai = Table.AddIndexColumn(ts, "i", 0, 1) in Table.RemoveColumns(Table.AddColumn(ai, "Duration", each try if [ShiftIndex]=ai[ShiftIndex]{[i]+1} then Duration.TotalMinutes(ai[StateDateTime]{[i]+1}-[StateDateTime]) else "" otherwise""),{"i"}) in count
Waiting for clarification on the structure of the table and the various cases that arise, I propose a different way that avoids the bottleneck of the List.Accumulate function and should deal with lists of thousands of rows in a few minutes.
Speaking of this assertion, could you please test the code on your tables and let us know if and how it goes?
For now I am providing only a sketch of the idea, which can be easily completed using the code of my first post which dealt with a group of lines related to a single shiftIndex.
let
ct = Table.TransformColumnTypes(tab,{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}),
idx=List.RemoveLastN(List.Distinct(ct[ShiftIndex]),1),
listRows=(ru,rl)=>
let
d=ru[StateDateTime],
dl=rl[StateDateTime],
shHour=if Time.Hour(dl) < 18 then 6 else 18,
shDay= if Time.Hour(d) > Time.Hour(dl) then 1 else 0,
uRow=ru&[StateDateTime=#datetime(Date.Year(d),Date.Month(d),Date.Day(d)+shDay,shHour,0,0)],
lRow=ru&[StateDateTime=#datetime(Date.Year(d),Date.Month(d),Date.Day(d)+shDay,shHour,0,0),ShiftIndex=ru[ShiftIndex]+1]
in {uRow,lRow},
lstR=List.Generate(
()=>[r=1,pos=List.PositionOf(ct[ShiftIndex],idx{0},Occurrence.Last), lr=listRows(ct{pos},ct{pos+1})],
each [r]<List.Count(idx),
each [r=[r]+1, pos=List.PositionOf(ct[ShiftIndex],idx{[r]},Occurrence.Last),lr=listRows(ct{[pos]},ct{[pos]+1}) ],
each [lr]
)
in
Table.Group(Table.Combine({Table.FromRecords(List.Combine(lstR)),tab}), {"ShiftIndex"}, {"shidx", each _})
the code refers to a a query (tab) contaning a table with starting data
Community ChampionHi Anonymous ,
I don't think that the main cause for the performance problems is the List.Accumulate Function, but instead the way you reference the previous row.
In this example, I'm using the method I've described here: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
It should perform well for this task, as long as you don't run it on multiple million rows:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc8xC8IwEAXgvyI3F3q5JKVmFjcXHUuGUIoG5JSS4N+3oUJMm+0NH/feDQMQtdi3hIQH1EYjNHALbg7xvSQBtilJZ0Qi18js+b4k2hJBRiZycZ7DxI7HqcrUys7OP+NcJ/1KcpmsEKXT5EcMp9eHs5H/m7F8S1UIlU07Io6/K7lJg7Vf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StateDateTime = _t, EquipmentState = _t, ShiftIndex = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}, "de-DE"),
GetPreviousRow = fnPreviousRow(#"Changed Type1"),
GetNextRow = fnNextRow( GetPreviousRow ),
#"Removed Columns" = Table.RemoveColumns(GetNextRow,{"StateDateTime.Prev.Next", "EquipmentState.Prev.Next", "ShiftIndex.Prev.Next"}),
#"Extracted Time2" = Table.TransformColumns(#"Removed Columns",{{"StateDateTime.Prev", DateTime.Time, type time}, {"StateDateTime.Next", DateTime.Time, type time}, {"StateDateTime", DateTime.Time, type time}}),
AddPosition = Table.AddColumn(#"Extracted Time2", "Position", each if [ShiftIndex.Prev] = null then "Last" else if [ShiftIndex.Prev] < [ShiftIndex] then "First" else if [ShiftIndex.Next] > [ShiftIndex] then "Last" else "Middle"),
AddPrevCurr = Table.AddColumn(AddPosition, "PrevCurr", each if [Position] = "First" then { "Previous", "Current"} else {"Current"}),
#"Expanded Custom2" = Table.ExpandListColumn(AddPrevCurr, "PrevCurr"),
AddStartShift = Table.AddColumn(#"Expanded Custom2", "StartShift", each if Time.From([StateDateTime]) >= #time(6,0,0) and Time.From( [StateDateTime] ) < #time(18,0,0) then #time(6,0,0) else #time(18,0,0)),
AddStartDuration = Table.AddColumn(AddStartShift, "StartDuration", each if [Position] = "First" and [PrevCurr] = "Previous" then [StartShift] else [StateDateTime]),
AddEndDuration = Table.AddColumn(AddStartDuration, "EndDuration", each if [PrevCurr] = "Previous" then [StateDateTime] else if [Position] = "Last" then (if [StartShift] = #time(6,0,0) then #time(18,0,0) else #time(6,0,0)) else [StateDateTime.Next]),
#"Inserted Time Subtraction" = Table.AddColumn(AddEndDuration, "Duration", each Duration.TotalMinutes(
if
[EndDuration] < [StartDuration] then ([EndDuration]
- [StartDuration]) + #duration(0,24,0,0) else [EndDuration] - [StartDuration]) ),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Time Subtraction",{"ShiftIndex.Next", "Position", "StartShift", "StartDuration", "EndDuration", "ShiftIndex.Prev", "StateDateTime.Next", "StateDateTime.Prev"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "State", each if [PrevCurr] = "Previous" then [EquipmentState.Prev] else [EquipmentState]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"State", "Duration", "ShiftIndex"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Duration", Int64.Type}})
in
#"Changed Type"
This code could probly be further optimzied and doesn't deliver correct results for first and last item, but should give a general hint on how a fast performing solution could look like.
This function code used is available in the file attached.
Hi ImkeF
I have already made use of this technique (if I understand correctly) in an old post here.
regarding the fact that list.accumulate is not very efficient for large tables, there is a post in which the topic has been deepened.
I have already mentioned it in this discussion.
To see how much of a bottleneck is using references to subsequent lines via index in comparison to the technique you are referring to, one could do some comparisons and see.- ImkeF
Yes Anonymous ,
the comparisons I did before posting here.
If the difference wouldn't have have been substantial, I would not have stepped into this thread (times are in seconds):
I stopped measuring after 2 minutes for the other List.Accumulate versions.
This is no surprise for me, as I've spent so many hours trying to debug performance problems with these approaches.
Haven't come across anything so far that comes even close to my approach with the shifted index for larger tables. - Hi ImkeFI probably misunderstood your remark about how to refer to the next line.
But your expression, that I report here, emphasized that the limitation is not due to the list.accumulate function but to the way of referring to the next line.<<...
I don't think that the main cause for the performance problems is the List.Accumulate Function, but instead the way you reference the previous row.
...>>The list.accumulate function has the only task of inserting the lines that facilitate the task of calculating the durations.
This calculation is done after list.accumulate has finished creating the complete table.
I thought you were referring to the durations calculation.
I haven't studied your code, I have to admit, and so I don't know how the shift technique can make the table creation by list.accumulate run faster.nt=List.Accumulate(idx,ct, (s,c)=> let pos=List.PositionOf(s[ShiftIndex],c,Occurrence.Last) in Table.InsertRows(s,pos+1,listRows(s{pos},s{pos+1}))), #"Added Index" = Table.AddIndexColumn(nt, "i", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Duration", each try if [ShiftIndex]=nt[ShiftIndex]{[i]+1} then Duration.TotalMinutes(nt[StateDateTime]{[i]+1}-[StateDateTime]) else "" otherwise"")
The comparison between list.accumulate and any other technique, I took for granted: list.accumulate is an elegant function for dealing with particular situations but with small tables / lists.I hope this succeeds in clarifying the meaning of my observation:To see how much of a bottleneck is using references to subsequent lines via index in comparison to the technique you are referring to, one could do some comparisons and see.PSI get help from google-translator and I don't have complete control of the expressions. - ImkeF
Yes Anonymous ,
we could have a misunderstanding here.
I stepped in after your post where you tried to improve the query execution time by replacing the List.Accumulate function approach by an approch using List.Generate.
And although List.Generate would perform a bit faster, you'd still have serious perfrormance problems as long as you don't solve the main cause for the slow query execution:The way you reference the previous row (in both of the solutions you've posted in this thread)
In the List.Accumulate-version:
And in the List.Generate-version:
Hope this clarifies it.
And to wrap up: I would not be surprised if your query with the List.Accumulate would run sufficiently fast, if you use my method to reference the previous row instead. I don't think, that List.Accumulate is the bottleneck for performance here.