Forum Discussion

ghdunn's avatar
ghdunn
Icon for Helper III rankHelper III
6 years ago
Solved

Conditional Merge or Table.SelectRows

I have just spent several hours working through a range of posts on Merges and Table.SelectRows but cant find anything quite right.   TableA Field1,Field2 a,w b,x c,y d,z   TableB Field1,Fi...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    try this

       cp = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2]))),
    tje = Table.ExpandTableColumn(cp, "join", {"Field1", "Field2", "Field3"}, {"join.Field1", "join.Field2", "join.Field3"}),
    fr = Table.SelectRows(tje, each ([join.Field3] <> null))
in
   fr

     

     

ghdunn's avatar
ghdunn
Icon for Helper III rankHelper III
6 years ago

Many thanks for the suggestion mahoneypat 

 

Your approach includes a number of steps at the end which I had considered but was hoping to avoid.  I was hoping there might be some more elegant syntax around a 'conditional join' where the join carried out the the OR test.  Either that or including the conditional logic of Table.SelectRows.

 

Do you think either of those approaches is possible in any way?

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

try this

   cp = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2]))),
    tje = Table.ExpandTableColumn(cp, "join", {"Field1", "Field2", "Field3"}, {"join.Field1", "join.Field2", "join.Field3"}),
    fr = Table.SelectRows(tje, each ([join.Field3] <> null))
in
   fr