Forum Discussion
Regroup Rows based on the difference between the end time of the first and start time of the second
- 5 years ago
sdlx I think I managed to get the desired outcome. I hope you understand all the steps described below:
File: https://1drv.ms/u/s!AgfuS40nNP3unGRlc32jpmfP9LU-?e=kumxf5
1 - Added Index Starting with 0
2 - Add Colum with the Previous End Time:
Table.AddColumn(#"Added Index", "Prev End Time", each if [Index]=0 then null else #"Added Index"{[Index]-1}[End Time])
3 - Calculate the difference betwen start time and previous end time:
Table.AddColumn(#"Added Custom", "Duration Since Prev End TIme", each [Start Time]-[Prev End Time])
4 - Add Column with the Duration SInce Previous End Time in Hours:
Table.AddColumn(#"Added Custom1", "Total Hours", each Duration.TotalHours([Duration Since Prev End TIme]), type number)
5 - To identify the if it is a new changeover, I created a formula that says "if device is diferent then device in previous row or the Duration SInce Previous End Time > 2 then 1 else 0:
Table.AddColumn(#"Inserted Total Hours", "Duration SInce Previous", each if [Index]=0 then 0 else if #"Inserted Total Hours"{[Index]-1}[Device] <> #"Inserted Total Hours"{[Index]}[Device] then 1 else if [Total Hours]>=2 then 1 else 0)
6 - Created a List with running sum of previous calculated column (this will be used to group and create a new Index):
List.Skip(List.Accumulate(#"Added Custom3" [Duration SInce Previous],{0}, (sum,index) => sum& {List.Last(sum) + index}))
7 - Merged the tables:
Table.FromColumns(Table.ToColumns(#"Added Custom3")&{Cumulative_new})
8 - Add column with the Duration SInce Previous End Time in Seconds:
Table.AddColumn(Add_columns, "Total Seconds", each Duration.TotalSeconds([Column9]), type number)
9 - Grouped by Index and Device with total seconds
= Table.Group(#"Inserted Total Seconds", {"Column12", "Column1"}, {{"Duration Sec", each List.Sum([Total Seconds]), type number}})
10 - Renamed columns (I lost the names when I merged the tables):
Table.RenameColumns(#"Grouped Rows",{{"Column12", "Index"}, {"Column1", "Device"}})
My solution was inspired by this: https://community.powerbi.com/t5/Desktop/Adding-conditional-index-based-on-changing-field-in-Power-Query/m-p/436418#M201257
It might help you to understand if I was not very clear in my description.
I hope that works 😅
Hi sdlx , a few questions:
Could you paste some lines of your data?
More than 2 lines summing less than 2 hours correspond to the same changeover?
Do you have more than 1 device in the same changeover? Or every time you have a device different from the previous line it means a new changeover?
Thanks