Split 1 row into multiple rows on hourly basis of datetime column in power bi

[Anonymous]

I have below dataset which include start and end time as mentioned below table.

ID	Start Date	End Date
abc	01-02-2021 08:00	01-02-2021 11:00
xyz	01-02-2021 08:00	01-02-2021 10:00

I want to split this into different hours such that one row contains only one hour of data. This is the dataframe I want to get.

ID	Start Date	End Date
abc	01-02-2021 08:00	01-02-2021 09:00
abc	01-02-2021 09:00	01-02-2021 10:00
abc	01-02-2021 10:00	01-02-2021 11:00
xyz	01-02-2021 08:00	01-02-2021 09:00
xyz	01-02-2021 09:00	01-02-2021 10:00

Need Help

Community Support Team

Thanks in advance

[Anonymous]

Its working thanks

[Anonymous]

Hi @Anonymous 

This is the table I created

Click transform data in power bi desktop

Use the following code to add a custom column

let

    StartDate = [Start Date],

    EndDate = [End Date],

    HourList = List.Transform({0..Duration.TotalHours(EndDate - StartDate)-1}, each StartDate + #duration(0, _, 0, 0))

in

    HourList

This is the result

Expand the custom column

Use the following code to add a custom column again as the end date

[Custom] + #duration(0, 1, 0, 0)

This is the result

^{Best Regards,}

^Jayleny

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

[Ahmedx · Accepted solution]

pls try code in power query