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,
thank you both for taking the time to help me.
CTozzi the rule for 2 line to have the same index is that they are correponding to the same device and with less than 2h between the end time of the first and start time of the second.
I have also filtered the rows so that they are showing in a chronoligcall order and regrouped by Devices.
Here is the table with raw data and the Index column I would like to create. Below you will find the desired output.
| Device | State | Start Time | End Time | Duration | Reason | INDEX |
| Pack_G10D5 | Setup | 02-01-21 23:05 | 02-01-21 23:48 | 2.555 | N. Changement de lot | 1 |
| Pack_G10D5 | Setup | 03-01-21 07:57 | 03-01-21 08:29 | 1.896 | N. Changement de lot | 2 |
| Pack_G10D5 | Setup | 03-01-21 09:04 | 03-01-21 09:10 | 338 | N. Changement de lot | 2 |
| Pack_G10D5 | Setup | 03-01-21 09:10 | 03-01-21 09:10 | 3 | Changement Format | 2 |
| Pack_G10D5 | Setup | 03-01-21 09:10 | 03-01-21 14:44 | 20.043 | N. Changement de lot | 2 |
| Pack_G10D5 | Setup | 03-01-21 14:44 | 03-01-21 14:44 | 6 | Changement Format | 2 |
| Pack_G10D5 | Setup | 03-01-21 14:44 | 03-01-21 14:44 | 1 | N. Changement de lot | 2 |
| Pack_G10D5 | Setup | 03-01-21 20:20 | 03-01-21 22:12 | 6.677 | N. Changement de lot | 3 |
| Pack_G10D5 | Setup | 03-01-21 22:59 | 04-01-21 02:00 | 10.828 | N. Changement de lot | 3 |
| Pack_G10D5 | Setup | 04-01-21 02:12 | 04-01-21 02:41 | 1.763 | N. Changement de lot | 3 |
| Pack_G10D5 | Setup | 04-01-21 09:50 | 04-01-21 13:25 | 12.925 | N. Changement de lot | 4 |
| Pack_G10D5 | Setup | 04-01-21 23:17 | 05-01-21 00:27 | 4.217 | N. Changement de lot | 4 |
| Pack_G10D5 | Setup | 05-01-21 00:58 | 05-01-21 01:52 | 3.240 | N. Changement de lot | 4 |
| Pack_G10D5 | Setup | 05-01-21 05:47 | 05-01-21 06:05 | 1.035 | N. Changement de lot | 5 |
| Pack_G10D5 | Setup | 05-01-21 06:05 | 05-01-21 06:05 | 0 | N. Changement de lot | 5 |
| Pack_G10D5 | Setup | 05-01-21 06:05 | 05-01-21 06:05 | 0 | N. Changement de lot | 5 |
| Pack_G10D5 | Setup | 05-01-21 06:19 | 05-01-21 07:25 | 3.960 | N. Changement de lot | 5 |
| Pack_G10D5 | Setup | 05-01-21 07:25 | 05-01-21 11:01 | 13.000 | Changement Format | 5 |
| Pack_G10D5 | Setup | 05-01-21 11:01 | 05-01-21 13:24 | 8.554 | N. Changement de lot | 5 |
| Pack_G10D5 | Setup | 05-01-21 15:33 | 05-01-21 15:37 | 254 | N. Changement de lot | 5 |
| Pack_G10D5 | Setup | 05-01-21 15:37 | 05-01-21 18:15 | 9.483 | N. Changement de lot | 6 |
| Pack_G10D5 | Setup | 05-01-21 22:31 | 05-01-21 22:52 | 1.206 | N. Changement de lot | 6 |
| Pack_G10D5 | Setup | 05-01-21 22:52 | 05-01-21 23:16 | 1.467 | N. Changement de lot | 7 |
| Pack_G10D5 | Setup | 05-01-21 23:36 | 06-01-21 00:31 | 3.295 | N. Changement de lot | 7 |
| Pack_G10D5 | Setup | 06-01-21 07:44 | 06-01-21 09:04 | 4.772 | N. Changement de lot | 8 |
| Pack_G10D5 | Setup | 06-01-21 09:04 | 06-01-21 10:00 | 3.347 | Changement Format | 8 |
| Pack_G10D5 | Setup | 06-01-21 10:00 | 06-01-21 11:08 | 4.137 | N. Changement de lot | 8 |
| Pack_G10D5 | Setup | 06-01-21 11:09 | 06-01-21 12:06 | 3.403 | N. Changement de lot | 8 |
| Pack_G10D5 | Setup | 06-01-21 12:06 | 06-01-21 12:33 | 1.600 | N. Changement de lot | 8 |
| Pack_G3P2 | Setup | 25-02-21 06:05 | 25-02-21 06:11 | 398 | Ouverture ligne | 9 |
| Pack_G3P2 | Setup | 25-02-21 06:11 | 25-02-21 13:50 | 27.501 | Ouverture ligne | 9 |
| Pack_G3P2 | Setup | 25-02-21 13:50 | 25-02-21 13:50 | 0 | Ouverture ligne | 9 |
| Pack_G3P2 | Setup | 25-02-21 13:50 | 25-02-21 13:50 | 0 | Ouverture ligne | 9 |
| Pack_G3P2 | Setup | 26-02-21 06:05 | 26-02-21 07:55 | 6.601 | Ouverture ligne | 10 |
| Pack_G3P2 | Setup | 26-02-21 09:07 | 26-02-21 09:07 | 0 | Changement de format | 10 |
| Pack_G3P2 | Setup | 26-02-21 16:15 | 26-02-21 21:35 | 19.191 | N. Changement de lot | 11 |
| Pack_G3P2 | Setup | 01-03-21 14:36 | 01-03-21 21:35 | 25.101 | N. Changement de lot | 12 |
| Pack_G3P2 | Setup | 01-03-21 21:35 | 01-03-21 21:35 | 0 | N. Changement de lot | 12 |
| Pack_G3P2 | Setup | 01-03-21 21:35 | 01-03-21 21:35 | 0 | N. Changement de lot | 12 |
Desired Output
| Index | Device | Duration (Sec) |
| 1 | Pack_G10D5 | 2.555 |
| 2 | Pack_G10D5 | 22287 |
| 3 | Pack_G10D5 | 19268 |
| 4 | Pack_G10D5 | 20382 |
| 5 | Pack_G10D5 | 26803 |
| 6 | Pack_G10D5 | 10689 |
| 7 | Pack_G10D5 | 4762 |
| 8 | Pack_G10D5 | 17259 |
| 9 | PACK_G3P2 | 27899 |
| 10 | PACK_G3P2 | 6601 |
| 11 | PACK_G3P2 | 19191 |
| 12 | PACK_G3P2 | 25101 |
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 😅
- sdlx5 years ago
Helper I
Hi,
First thanks, it definitely work.
The issue is that I have big amount of data, over 1.000 rows and it seems to be really slow to load, would you have an idea to speed up the process ?