Forum Discussion

SvenP's avatar
SvenP
Frequent Visitor
6 years ago
Solved

Fill in hours between datetimes

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/2...
Need Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    Sven,

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

SvenP's avatar
SvenP
Frequent Visitor
6 years ago

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's avatar
HotChilli
Community Champion
6 years ago

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