Forum Discussion
Using different fields from a table to select records from another table in M
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
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
2 Replies
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
