Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Generate running time rows/table

Hello everyone,   I am trying to get the running time of a machine & I have only the stoppages recorded + few details on the starting/ending time. Facts that I know is that on a daily basis the ma...
  • HotChilli's avatar
    5 years ago

    The algorithm would be something like:   (Power Query)

    Take the original 'Date Fin' Column and add in a record for 0700 for each date . This will end up as the 'Date Debut' column

    -> So take the column , separate date and time, remove the time column. Remove duplicates. Add a column for time 0700.  Merge columns to get a table with one column which is 0700 for each date.

    Append this table to a table that holds the original 'Date Fin' column. Sort it by datetime.

     

    You'll want to do a similar thing to the original 'Date Debut' column , by adding a 1500 record for each day to make the final 'Date Fin' column.

     

    You then have to find a way of Merging the 2 tables to get the records to line up.  I'm thinking of a new column with just the Date in each table.  Create an index column with in each day so

    1.9.20 0700        1.9.20      1

    1.9.20 0805        1.9.20      2

    .....

    2.9.20 0700        2.9.20     1

     

     

    You can then Merge the 2 tables on Date and Index columns.  After that, you calculate the duration column. 

     

    Take each step carefully.  Good luck.