Forum Discussion

mouzzampk's avatar
mouzzampk
Helper I
2 years ago
Solved

List time in rows between start and end time

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...
General Comment
Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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

m_dekorte's avatar
m_dekorte
Resident Rockstar
2 years ago

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's avatar
mouzzampk
Helper I
2 years ago

Thank you