List time in rows between start and end time

[mouzzampk]

Hi, how can I convert the data so it looks like that. Handling Time is not important and I can remove it if its making its difficult to convert.

29/09/2023 15:47 3391 5

29/09/2023 15:48 3391 5

29/09/2023 15:49 3391 5

29/09/2023 15:50 3391 5

29/09/2023 15:51 3391 5

29/09/2023 15:52 3391 5

29/09/2023 15:41 4684 3

29/09/2023 15:42 4684 3

29/09/2023 15:43 4684 3

29/09/2023 15:44 4684 3

[m_dekorte · Accepted solution]

Hi @mouzzampk,

If you need to fill the gabs between start- and end time you could use the List.Times function

let
    Source = Table.FromRows(
        {
            {#date(2023, 9, 29), #time(15,47,00), 3391, 5},
            {#date(2023, 9, 29), #time(15,41,00), 4684, 3}
        }, type table
        [Date = date, Start Time = time, Officer = text, Handling Time = number]
    ),
    ListTimes = Table.AddColumn( Source, "Time", each 
        List.Times([Start Time], [Handling Time]+1, #duration(0, 0, 1, 0)), type {time}
    ),
    ExpandTimes = Table.ExpandListColumn(ListTimes, "Time")[[Date], [Time], [Officer]]
in 
    ExpandTimes

with this result

I hope this is helpful

[mouzzampk]

Thank you 

[AlienSx]

Hi, @mouzzampk select Start Time and End Time. Then unpivot them.

[mouzzampk]

Thanks for the reply but basically I was looking to fill the gaps between two times and m_dekorte solution worked great. 

Thanks again