Look uo for matching fields from two different data sets

[dcr007]

For each 'cid' in Table 1 , I want to look up for a match in table 2  against the 'companyid,'  . Table 3 should contain all the matches for the companyid field from Table2 .

Table1

dc , cid, name 

--------------------

dc1, DC9098, xyc

dc2, DC9081, xyc

dc3, DC9041, xyc

Table2 

id , companyid, client, date 

-----------------------------------

1, DC9098, SPA,10/12/20

2, DC9081,APPL,30/31/19

3, DC9081,GOOG,30/31/19

4, DC9081,RAS,10/31/19

5, DC9031,RAS,10/31/19

Table 3 

id , companyid, client, date 

-----------------------------------

1, DC9098, SPA,10/12/20

2, DC9081,APPL,30/31/19

3, DC9081,GOOG,30/31/19

4, DC9081,RAS,10/31/19

[Greg_Deckler · Accepted solution]

@dcr007 Maybe:

Table 3 =
  VAR __cids = ALL('Table1'[cid])
RETURN
  FILTER(ALL('Table2'),[companyid] IN __cids)

[dcr007]

Hey @Greg_Deckler Thanks that helped ! Also I figured out the creating a view using merge queries to be effecient in terms of time space complexity. Like so :

= Table.NestedJoin(Table2, {"companyId"}, Table1, {"companyId"}, "Table1", JoinKind.Inner)

src: https://docs.microsoft.com/en-us/power-query/merge-queries-overview