Forum Discussion

madman12's avatar
madman12
Frequent Visitor
2 years ago
Solved

Split Start-End timestamp Rows into per minute rows

I have one table such as:

 

PersonLocationStartEnd
Person ALocA29/04/2024 13:0029/04/2024 15:57
Person ALocR25/04/2024 18:0029/04/2024 17:14
Person GLocA28/04/2024 21:0028/04/2024 21:02

 

I want to split rows into minute granularity rows, take last row above as example the result would be:

 

PersonLocationStartEndMinute
Person GLocA28/04/2024 21:0028/04/2024 21:0228/04/2024 21:00
Person GLocA28/04/2024 21:0028/04/2024 21:02

28/04/2024 21:01

Person GLocA28/04/2024 21:0028/04/2024 21:0228/04/2024 21:02

 

Its like a join,except instead of it being a exact match join to one of the timestamps its a between join with a date/time table that holds a row for each minute of the day. At least thats how I'm interpreting it.

 

I can do the splitting into minute granularity rows in SQL to allow the usual exact match of a join in PBI to work (join on between) however the more people, locations and dates covered the larger the extract so its limited in scale.

 

Wondering if powerbi would be able to handle that part so SQL would only need to get a row for each whole period as shown in first table.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi madman12 ,

    Below is my table:

    This situation can be realised using the m-language:

    You can create a custom column:

    List.Transform(
    List.DateTimes(
        [Start], 
        Duration.TotalMinutes([End] - [Start]) + 1, 
        #duration(0, 0, 1, 0)
    ), 
    each DateTime.ToText(_, "dd/MM/yyyy HH:mm")
))

    The final output is shown in the following figure:

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi madman12 ,

    Below is my table:

    This situation can be realised using the m-language:

    You can create a custom column:

    List.Transform(
    List.DateTimes(
        [Start], 
        Duration.TotalMinutes([End] - [Start]) + 1, 
        #duration(0, 0, 1, 0)
    ), 
    each DateTime.ToText(_, "dd/MM/yyyy HH:mm")
))

    The final output is shown in the following figure:

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.