Forum Discussion

sdlx's avatar
sdlx
Icon for Helper I rankHelper I
5 years ago
Solved

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

 

thanks in advance for your help

Need Help
Show and Tell
Tips and Tricks
  • CTozzi's avatar
    CTozzi
    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 😅

5 Replies

  • CTozzi's avatar
    CTozzi
    Icon for Resolver I rankResolver I
    5 years ago

    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

    • sdlx's avatar
      sdlx
      Icon for Helper I rankHelper I
      5 years ago

      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.

      DeviceStateStart TimeEnd TimeDurationReasonINDEX
      Pack_G10D5Setup02-01-21 23:0502-01-21 23:482.555N. Changement de lot1
      Pack_G10D5Setup03-01-21 07:5703-01-21 08:291.896N. Changement de lot2
      Pack_G10D5Setup03-01-21 09:0403-01-21 09:10338N. Changement de lot2
      Pack_G10D5Setup03-01-21 09:1003-01-21 09:103Changement Format2
      Pack_G10D5Setup03-01-21 09:1003-01-21 14:4420.043N. Changement de lot2
      Pack_G10D5Setup03-01-21 14:4403-01-21 14:446Changement Format2
      Pack_G10D5Setup03-01-21 14:4403-01-21 14:441N. Changement de lot2
      Pack_G10D5Setup03-01-21 20:2003-01-21 22:126.677N. Changement de lot3
      Pack_G10D5Setup03-01-21 22:5904-01-21 02:0010.828N. Changement de lot3
      Pack_G10D5Setup04-01-21 02:1204-01-21 02:411.763N. Changement de lot3
      Pack_G10D5Setup04-01-21 09:5004-01-21 13:2512.925N. Changement de lot4
      Pack_G10D5Setup04-01-21 23:1705-01-21 00:274.217N. Changement de lot4
      Pack_G10D5Setup05-01-21 00:5805-01-21 01:523.240N. Changement de lot4
      Pack_G10D5Setup05-01-21 05:4705-01-21 06:051.035N. Changement de lot5
      Pack_G10D5Setup05-01-21 06:0505-01-21 06:050N. Changement de lot5
      Pack_G10D5Setup05-01-21 06:0505-01-21 06:050N. Changement de lot5
      Pack_G10D5Setup05-01-21 06:1905-01-21 07:253.960N. Changement de lot5
      Pack_G10D5Setup05-01-21 07:2505-01-21 11:0113.000Changement Format5
      Pack_G10D5Setup05-01-21 11:0105-01-21 13:248.554N. Changement de lot5
      Pack_G10D5Setup05-01-21 15:3305-01-21 15:37254N. Changement de lot5
      Pack_G10D5Setup05-01-21 15:3705-01-21 18:159.483N. Changement de lot6
      Pack_G10D5Setup05-01-21 22:3105-01-21 22:521.206N. Changement de lot6
      Pack_G10D5Setup05-01-21 22:5205-01-21 23:161.467N. Changement de lot7
      Pack_G10D5Setup05-01-21 23:3606-01-21 00:313.295N. Changement de lot7
      Pack_G10D5Setup06-01-21 07:4406-01-21 09:044.772N. Changement de lot8
      Pack_G10D5Setup06-01-21 09:0406-01-21 10:003.347Changement Format8
      Pack_G10D5Setup06-01-21 10:0006-01-21 11:084.137N. Changement de lot8
      Pack_G10D5Setup06-01-21 11:0906-01-21 12:063.403N. Changement de lot8
      Pack_G10D5Setup06-01-21 12:0606-01-21 12:331.600N. Changement de lot8
      Pack_G3P2Setup25-02-21 06:0525-02-21 06:11398Ouverture ligne9
      Pack_G3P2Setup25-02-21 06:1125-02-21 13:5027.501Ouverture ligne9
      Pack_G3P2Setup25-02-21 13:5025-02-21 13:500Ouverture ligne9
      Pack_G3P2Setup25-02-21 13:5025-02-21 13:500Ouverture ligne9
      Pack_G3P2Setup26-02-21 06:0526-02-21 07:556.601Ouverture ligne10
      Pack_G3P2Setup26-02-21 09:0726-02-21 09:070Changement de format10
      Pack_G3P2Setup26-02-21 16:1526-02-21 21:3519.191N. Changement de lot11
      Pack_G3P2Setup01-03-21 14:3601-03-21 21:3525.101N. Changement de lot12
      Pack_G3P2Setup01-03-21 21:3501-03-21 21:350N. Changement de lot12
      Pack_G3P2Setup01-03-21 21:3501-03-21 21:350N. Changement de lot12

      Desired Output

      IndexDeviceDuration (Sec)
      1Pack_G10D52.555
      2Pack_G10D522287
      3Pack_G10D519268
      4Pack_G10D520382
      5Pack_G10D526803
      6Pack_G10D510689
      7Pack_G10D54762
      8Pack_G10D517259
      9PACK_G3P227899
      10PACK_G3P26601
      11PACK_G3P219191
      12PACK_G3P225101
      • CTozzi's avatar
        CTozzi
        Icon for Resolver I rankResolver I
        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 😅