How do I get the list of date/times between 2 date time columns?

[E_K_]

I have data from different sources and require to be able to make a relationship between a date time column from 2 different data sets to another dataset that has various conditional columns define whether in or out of a defined window.

These windows are defined by date and time, so the Y/N indicator columns do not work properly for filtering,for example the window may start at 4am on 12/11/2022,however the item associated as a result with have both a Y and a N defined for 12/11/2022 so from the perspective of the datasets that have the relationship set up resolve to whichever appears first - Y or N.

I have tried the approach of listing dates between the 2 date time columns and setting up the relationship that way however that had lead to the above issue.

Below is a snippet of what I have tried to do, and the idea of the model I am working with

I am aware that listing all the dates and time (by start of hour,minutes and seconds not necessary) between 2 dates will make the window defining dataset very large, however if another approach can be suggested - let me know.

[Nikkystar]

Hi @lbendlin  @needhelp Can you help out with this issue? 
Currently I am trying to develop a visual which is similar to outlook calendar. But I could bring vaule only to one cell based on the time. There is a start time and end time. Is there any way I can bring up the value continuously from start time to end time just like in the calendar. I have attctched the snip here. 

[lbendlin]

Better use a Gantt chart, or Deneb with X1-X2 lines.

[E_K_]

Thanks - however how do I get the list of dates + hours?

I am only able to get the list of dates with every date followed by eg 12/11/2022 00:00, 13/11/2022 00:00 etc rather than eg 12/11/2022 00:00, 12/11/2022 01:00, etc

using the below:

#"Added Custom1" = Table.AddColumn(Custom1, "Start Time", each [start_time]-#duration(0,4,0,0)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Start Time", type datetime}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "End Time", each [end_time]-#duration(0,4,0,0)),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"End Time", type datetime}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type3",{"start_time", "end_time"}),
#"Added Conditional Column2" = Table.AddColumn(#"Removed Columns3", "Sensitive Client?", each if [title] = "Sensitive Client List" then "Y" else "N"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Added Conditional Column2", "Start Time", "Start Time - Copy"),
#"Changed Type4" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Start Time - Copy", Int64.Type}}),
#"Duplicated Column2" = Table.DuplicateColumn(#"Changed Type4", "End Time", "End Time - Copy"),
#"Changed Type5" = Table.TransformColumnTypes(#"Duplicated Column2",{{"End Time - Copy", Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type5", "Dates between", each {Number.From([#"Start Time - Copy"])..Number.From([#"End Time - Copy"])}),
#"Expanded Dates between" = Table.ExpandListColumn(#"Added Custom3", "Dates between"),
#"Changed Type6" = Table.TransformColumnTypes(#"Expanded Dates between",{{"Dates between", type datetime}}),

[lbendlin · Accepted solution]

Here's the basic logic.  Create a calendar table that includes Hour slots.  Then do an intersect with your fact datetime ranges.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzTSNzIwMlKK1YFwjVG5JlBuLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {0..23}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Hour Slot", each DateTime.From([Date])+ #duration(0,[Custom],0,0),type datetime)
in
    #"Added Custom1"

Or modify the List.Generate with hourly slots between your timestamps.

[lbendlin]

What you can do is cheat and decrease the output granularity. For example omitting the seconds will shave off two orders of magnitude.

There are other approaches of using separate data and time tables, but these don't seem to apply in your case.