Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Intersect and create a new table.

I have two tables.

Table 1

idtime_stampproduct_name
   

 

Table 2

idtime_stampproduct_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

idtable1[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. 

 

Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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"
  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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:

     

     

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.