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 sev...
  • 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 😅