Create a list from two timestamps in power query

[arsm]

Hello, I'm trying to create a list from two timestamp columns in power query 

I have two columns with date and hour 

Column 1                                           Column 2 

12/14/2023 10:00:00 AM            12/15/2023 13:00:00 AM 

Output im looking for in column 3 is list of date and hours 

                                                       Column 3 

                                                      12/14/2023 10:00 

                                                      12/14/2023 11:00 

                                                       12/14/2023 12:00

                                                                    .

                                                                    .

                                                                    .  

                                                          12/15/2023 13:00

I tried Number.from (coumn 1)..Number from(column2) and it's not working. it is working when column only has date or only hour. 

Is there a way to create a list with both date hour ...please help 

[arsm]

Hi Jennratten, Thank you for the code, is there a way to list hours  

this list shows as 12/14/2024 10:00:00 AM

                            12/15/2024 11:00:00 AM

Insted how can i list same date and list hours 

                            12/14/2024 10:00:00 AM

                            12/14/2024 11:00:00 AM

                            12/14/2024 12:00:00 PM....... 12/15/2024 1:00:00 PM

Please let me know if i can modify this code. 

[arsm]

Hi @jennratten , Thank you for the code, is there a way to list hours  

this list shows as 12/14/2024 10:00:00 AM

                            12/15/2024 11:00:00 AM

Insted how can i list same date and list hours 

                            12/14/2024 10:00:00 AM

                            12/14/2024 11:00:00 AM

                            12/14/2024 12:00:00 PM....... 12/15/2024 1:00:00 PM

Please let me know if i can modify this code. 

[Anonymous]

Hi @arsm ,

@jennratten Thanks for your reply!

@arsm Just change the answer of @jennratten 's into:

= Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([Column1], Number.From([Column2]) ,#duration(0,1,0,0)))

Best Regards,
Dino Tao
If this post helps, then please consider Accept both of the answers as the solution to help the other members find it more quickly.

[arsm]

@Anonymous  Thank you for replying 

I was able to create a list but list wont stop at 12/15/2024 1:0:0 PM (column 2), it keeps going. 

how can i end the list at column 2 timestamp 

[Anonymous · Accepted solution]

Hi @arsm ,

Please change the M function into this:

Table.AddColumn(#"Changed Type", "Custom.1", each List.DateTimes([Column1], Duration.TotalHours([Column2] - [Column1]) + 1 ,#duration(0,1,0,0)))

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

[jennratten]

Hi @arsm - this is how you can do it.

Table.AddColumn(#"Changed Type", "Duration", each List.DateTimes([Column1], Number.From([Column2]) ,#duration(1,1,0,0)))