Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors