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?

Question

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

• Workblocks always occur within a single day, and the first timestamp of the day marks the start of a workblock.
• First timestamp of the day is always a startpoint of a workblock
• There might be errors in the data, but I want to focus on calculating workblock durations first -> if a day is not complete (no even number of timestamps) just leave this one out for now
• The office column shows if i worked in the office or from home (1 = in the office)

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

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

• date [dd.mm.yyy]
• office [0,1]
• duration [hh:mm]

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

From the herewith ilustrated blocks:

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?

Answer 1

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:

• Group on date and keep all rows (Rows1)
• Add a new column (Rows2) which takes the odd rows of timestamp and Zip with the even rows. Then transform with subtracting the two. Then create table of the previous transformed list with the odd rows of the office column, and then add the index.
• Remove Rows1 and expand Rows2.

Result