Fill in hours between datetimes

[SvenP]

Hi guys,

I have a table that shows entry and exit datetimes of a car park. I would now like to use Power Query to create a new line for every hour a car was parked there. Example:

Entry: 01/01/2020 at 9:30 am

Exit: 01/01/2020 at 4.30 pm

Expected result: 7 lines in total.

Pretty much this (unfortunately @Nolock did not explain how he did it): Here is the link to the original post 

I know how to do it with dates so that PBI generates a line for every day, but I do not know how to do it so that hours are added.

Can anybody help please?

Thanks!

Sven

[HotChilli · Accepted solution]

Sven,

Have you tried the power Query code in the thread you linked to (it's further down)?

[SvenP]

Hey @HotChilli ,

yeah I did. The thing is I dont have any clue about M so I was guessing around. Bcause it never worked, I made this post. Finally it seems to work but I cant really explain why and what I did. For now, I am happy with it though as it delivers the expected result.

Thanks a lot for looking into it!

Cheers, Sven 

[HotChilli]

I'm glad that you got it sorted.

A brief summary of the algorithm is

Take each row of data (it should have a starttime and endtime on each row)

Make a list which contains values made by adding an hour to the starttime repeatedly until we reach the endtime.

Add each list to the end of each row that it is calculated from.

Expand each list into new rows which contain the original row and a single list entry

As long as you have data in rows that is similar to the example, you can swap in the starttime and endtime column names in the correct places and it should work