datetimepowerbicalculationduration

How can I calculate the duration of workblocks in Power BI when I only have a table with timestamps and no explicit start or end information?


I have an Excel table with columns for date, office, and timestamps manually inserted:

enter image description here

I've loaded the data into Power BI, performed indexing, and created a combined time/date column.

enter image description here

I'd like to create a new table in Power BI that shows a row for each workblock with the following columns:

Illustration of what the table could look like in power bi:

enter image description here

From the herewith ilustrated blocks:

enter image description here

If easier the durations can be in hh:mm or in hh.min format at the end i need both and think im able to convert vice versa.

Thank you so much <3

What i already tried is using lag and i was thinking of generating a new column with the previous timestamp in it whenever its on same day. this would work but also would give me blocks for the breaks. which is also nice but then it should mark them as work or break so i can then use them to illustrate. But how do i know when a block is work or if it is break time?


Solution

  • Given a starting table of:

    date office timestamp
    03/01/2024 1 10:31
    03/01/2024 1 12:21
    03/01/2024 1 12:50
    03/01/2024 1 16:06
    04/01/2024 1 09:06
    04/01/2024 1 12:19
    04/01/2024 1 12:47
    04/01/2024 1 15:56
    05/01/2024 1 07:15
    05/01/2024 1 12:15
    05/01/2024 1 12:45
    05/01/2024 1 16:00
    09/01/2024 1 08:48
    09/01/2024 1 12:14
    09/01/2024 1 12:51
    09/01/2024 1 15:57
    09/01/2024 0 19:00
    09/01/2024 0 19:30
    10/01/2024 1 07:59
    10/01/2024 1 12:17
    10/01/2024 1 12:56
    10/01/2024 1 17:44
    10/01/2024 0 18:20
    10/01/2024 0 19:30

    Try the following:

    let
      Source = Table,
      #"Grouped rows" = Table.Group(Source, {"date"}, {{"Rows1", each _, type nullable table}}),
      #"Added custom" = Table.AddColumn(#"Grouped rows", "Rows2", each 
        let
          dList = List.Transform(List.Zip({ List.Alternate([Rows1][timestamp], 1, 1, 1), List.Alternate([Rows1][timestamp], 1, 1) }), each _{1} - _{0} ),
          dTbl = Table.FromColumns({List.Alternate([Rows1][office], 1, 1, 1), dList}, {"office", "duration"}),
          addIndex = Table.AddIndexColumn(dTbl, "block_nr", 1, 1, Int64.Type)
        in
          addIndex
      ),
      #"Removed columns" = Table.RemoveColumns(#"Added custom", {"Rows1"}),
      #"Expanded Rows2" = Table.ExpandTableColumn(#"Removed columns", "Rows2", {"office", "duration", "block_nr"}, {"office", "duration", "block_nr"})
    in
      #"Expanded Rows2"
    

    Explaination:

    Result

    enter image description here