Forum Discussion

jones_lilly's avatar
jones_lilly
Frequent Visitor
4 years ago
Solved

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

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

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

    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]))

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

    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's avatar
    jennratten
    Super User
    4 years ago

    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"