Forum Discussion

mouzzampk's avatar
mouzzampk
Icon for Helper I rankHelper 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 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

 

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

4 Replies

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident 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
      Icon for Helper I rankHelper I
      2 years ago

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

       

      Thanks again