cancel
Showing results for
Did you mean:
Helper I

## Regroup Rows based on the difference between the end time of the first and start time of the second

Hi Everybody,

I have a set of data with each rows providing the start date and time and end date and time of part of changeover of a line. One Full changeover of a line can be split in one to several differents rows.

What I want to obtain is the total duration per changeover per devices in order to plot the different durations and see the variablity of total changeover.

My idea was to do a "Custom" group by.

To do so I would have add a column with an index with the rows corresponding to the first changeover with "1", the rows corresponding to the second changeover with "2" etc.

The trigger to decide if the rows correpond to one or another changeover is the time between the end time of the a row and the strat time of the next one.

Two consecutives rows will correpond to 2 differents changeover if they are spaced by at least 2h.

I have absolutely no idea on how to that and if it possible:

Here is a look at my Raw data:

raw data

1 ACCEPTED SOLUTION
Resolver I

@sdlx I think I managed to get the desired outcome. I hope you understand all the steps described below:

1 - Added Index Starting with 0

2 - Add Colum with the Previous 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:

8 - Add column with the Duration SInce Previous End Time in Seconds:

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-Q...

It might help you to understand if I was not very clear in my description.

I hope that works 😅

5 REPLIES 5
Super User

could you please provide the expected output in excel?

Proud to be a Super User!

Helper I

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
Resolver I

@sdlx I think I managed to get the desired outcome. I hope you understand all the steps described below:

1 - Added Index Starting with 0

2 - Add Colum with the Previous 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:

8 - Add column with the Duration SInce Previous End Time in Seconds:

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-Q...

It might help you to understand if I was not very clear in my description.

I hope that works 😅

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 ?

Resolver I

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

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors