Forum Discussion
Look uo for matching fields from two different data sets
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
Anonymous Maybe:
Table 3 = VAR __cids = ALL('Table1'[cid]) RETURN FILTER(ALL('Table2'),[companyid] IN __cids)
2 Replies
- Greg_Deckler
Community Champion
Anonymous Maybe:
Table 3 = VAR __cids = ALL('Table1'[cid]) RETURN FILTER(ALL('Table2'),[companyid] IN __cids)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