cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Merleau
Helper II
Helper II

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

1 ACCEPTED SOLUTION
PC2790
Solution Sage
Solution Sage

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:

PC2790_0-1614143114709.png

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

 

View solution in original post

2 REPLIES 2
Merleau
Helper II
Helper II

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
Solution Sage
Solution Sage

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:

PC2790_0-1614143114709.png

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

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors