Using different fields from a table to select records from another table in M

[Merleau]

Hello,

I need help in using two different attributes from a table to select records from another table.

I have two starting tables.

The main one – table A - looks like:

ID	Other_columns	type	Zipcode	parent_Id
dis1		dist	10D1
Id2			1000A2	dis2
dis2		dist	10D2
Id4			1000A4	dis1
Id5			1000A5
Id6			1000A6
dis3		dist	10D3

And the other table B (no "dis" as ID):

ID	Other_columns	parent_Id	Zipcode
Id1			1000A1
Id2		dis2	1000A2
Id3			1000A3
Id4		dis1	1000A4

I would like to select all rows from my main table that have their IDs in Table B and also the ones that have their ID in Table B’s parend.id field

The final table should look like:

ID	Other_columns	type	Zipcode	parent_Id
dis1		dist	10D1
dis2		dist	10D2
Id1			1000A1
Id2			1000A2	dis2
Id3			1000A3
Id4			1000A4	dis1

To solve this, I tried the following. I created a list from table B, parent.id. Then, I created another list from Table B, ID field. I concatenated both lists and converted the result into a table.

I used the new table – which has only primary keys - to select records in my main table A.

This method seems very long. Can somebody pls suggest a more concise approach? I need the solution in M.

Thank you

[Merleau]

Great solution @PC2790 

And you are correct about my output. What I posted was wrong.

Your approach is much cleaner and faster.

Thank you.

[PC2790 · Accepted solution]

Hello @Merleau ,

Another approach of achieving this can be as below:

1) Merge the two tables "as New" as Inner Join on the basis of ID

= Table.NestedJoin(TableA, {"ID"}, TableB, {"ID"}, "TableB", JoinKind.Inner)

2) Again merge the two tables "as New" on the basis of ID in Table A and parent_id in Table B:

= Table.NestedJoin(TableA, {"ID"}, TableB, {"parent_Id"}, "TableB", JoinKind.Inner)

3) Then Append the two outcome tables to get the desired result:

= Table.Combine({Merge1, Merge2})

The end result would look something like this:

This is somewhat differnet from the end result shown by you.

Please recheck your requirement and the result given by you as there seems to be a mismatch.

If you are looking to get the result from Table A that are presnet in Table B, the records wit ID1 and ID3 should not be part of the reuslt.

Please confirm.

I hope this might solve your purpose.

Thankyou