Intersect and create a new table.

[Anonymous]

I have two tables.

Table 1

id	time_stamp	product_name

Table 2

id	time_stamp	product_name

I would like to create a new table

where id column should contain table2[id] where table2[id] is present in table1[id],

and table1[time_Stamp] > table2[time_stamp]

result calculated table

id	table1[time_stamp]	table2[time_stamp]	diff[table2[time_stamp] - table1[time_stamp]]	table1[product_name]	table2[product_name]

Can anyone please give me some idea where should I start?

I know how to get table2[id]s present in table1[id] using intersect. But don't know to proceed next. 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

You may create a new query with the following m codes in 'Advanced Editor'.

let
    Source = Table.AddColumn(Table1,"New",each Table.SelectRows(Table2,(x)=>x[id]=_[id] and x[time_stamp]<_[time_stamp])),
    #"Expanded New" = Table.ExpandTableColumn(Source, "New", {"id", "time_stamp", "product_name"}, {"T2.id", "T2.time_stamp", "T2.product_name"}),
    Custom1 = Table.SelectRows(#"Expanded New",each [T2.id]<>null)
in
    Custom1

Result:

Or you may try creating a calculated table with following dax.

Table = 
SELECTCOLUMNS(
    FILTER(
        CROSSJOIN(
            Table1,
            Table2
        ),
        Table1[id]=Table2[id]&&Table1[time_stamp]>Table2[time_stamp]
    ),
    "T1_id",Table1[id],
    "T2_id",Table2[id],
    "T1_time",Table1[time_stamp],
    "T2_time",Table2[time_stamp],
    "T1_pname",Table1[product_name],
    "T2,pname",Table2[product_name]
)

Result:

Best Regards

Allan

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

[Fowmy]

@Anonymous 

If you need to implement a DAX solution, create a new table with the following code:

New Table = 

SELECTCOLUMNS(
    FILTER(
        GENERATE( 
            Table1, 
            SELECTCOLUMNS(
                Table2,
                "_T2 ID", Table2[id],
                "_T2 Time", Table2[time_stamp],
                "_T2 Product Name", Table2[product_name]
            )
        ),
        Table1[id] = [_T2 ID] && Table1[time_stamp] > [_T2 Time]
    ),
    "T1 ID", Table1[id], 
    "T1 Time", Table1[time_stamp],
    "T2 Time", [_T2 Time],
    "Time Diff",DATEDIFF(Table1[time_stamp] , [_T2 Time],MINUTE),
    "T1 Product Name",Table1[product_name],
    "T2 Product Name",[_T2 Product Name]
)

These are the two tables I made for demo:

[ebeery]

@Anonymous Maybe this is just my personal preference, but I would use Power Query for this, unless there's a specific reason not to.

With Table 1 and 2 like you've described them, basic steps would be to inner join Table 2 with Table 1, expand table 1, and filter rows based on Table1.time_stamp > Table2.time_stamp.

Power Query code would look something like below.

let
  Source = Table.NestedJoin(Table2, {"id"}, Table1, {"id"}, "Table1", JoinKind.Inner),
  #"Expanded Table1" = Table.ExpandTableColumn(
    Source,
    "Table1",
    {"id", "Table1.time_stamp", "Table1.product_name"},
    {"id.1", "Table1.time_stamp", "Table1.product_name"}
  ),
  #"Filtered Rows" = Table.SelectRows(
    #"Expanded Table1",
    each [Table1.time_stamp] > [Table2.time_stamp]
  )
in
  #"Filtered Rows"