Combine 2 tables based on datetime in one table falling between start/end datetime columns in other

[jones_lilly]

Hi,

The following Table A has a start and end DateTime column (Event Frame Start Time & Event Frame End Time):

I then have the following Table B: 

Is it possible to create a new table (or combine), which contains the ID_1, Event Frame Start Time, Event Frame End Time from Table A and the Date_Time & Category from Table B IF any of the Date_Time rows falls between Event Frame Start Time and Event Frame End Time?

Thanks,

Conor

[wdx223_Daniel · Accepted solution]

New DAX Query=GENERATE(TableA,FILTER(SUMMARISE(TableB,TableB[Date_Time],TableB[Category]),TableB[Date_Time]>=TableA[Event Frame Start Time]&&TableB[Date_Time]<=TableA[Event Frame End Time]))

[jennratten]

Hello - this is how you can add ID_1, StartTime and EndTime from TableA to TableB for rows in which the DateTime is between the Start and End Times from TableA.

TableA

TableB Before

TableB After

Table A Script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0dASShvqG+kYGRgYKJlYGBgW5YBFjqJChEVBMASgYqxOtZASUcnJyApJGyFoUwHqM9I0s0PXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ID_1 = _t, StartTime = _t, EndTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ID_1", type text}, {"StartTime", type datetime}, {"EndTime", type datetime}})
in
    #"Changed Type"

TableB Script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQMLEyMAAihQBfJR0l54zEvPRUIMPLxd9VKVYnWskEps7QCKSqIBdTVSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, Event = _t, User = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"Event", type text}, {"User", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(TableA, (x)=> x[StartTime]<=[DateTime] and x[EndTime]>=[DateTime])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID_1", "StartTime", "EndTime"})
in
    #"Expanded Custom"