Split Start-End timestamp Rows into per minute rows

[madman12]

I have one table such as:

Person	Location	Start	End
Person A	LocA	29/04/2024 13:00	29/04/2024 15:57
Person A	LocR	25/04/2024 18:00	29/04/2024 17:14
Person G	LocA	28/04/2024 21:00	28/04/2024 21:02

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

Person	Location	Start	End	Minute
Person G	LocA	28/04/2024 21:00	28/04/2024 21:02	28/04/2024 21:00
Person G	LocA	28/04/2024 21:00	28/04/2024 21:02	28/04/2024 21:01
Person G	LocA	28/04/2024 21:00	28/04/2024 21:02	28/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.

[Anonymous · Accepted solution]

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.