Forum Discussion
How to join a table with a subquery using power query M
I don't see why not, it's M code, you can pretty much do whatever you want. See my technique here:
https://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx
Well, if you don't want to write M code to keep it all in one query, you could simply create a new reference query to Table1. Right click Table1 query and choose "Reference". Then in that query right-click the EmpID column and choose to remove duplicates. Then just use that table in your merge query. Or you could just write those steps in M and keep it all in a single query without creating the table, but however you want to do it. For example something like this would probably work:
= Table.NestedJoin(Table.Distinct(Table1,{"EmpID"}),{"EmpID"},Table2,{"EmpID"},"Table2",JoinKind.LeftOuter)
Hi LivioLanzo and ImkeF, thanks for your help!
I want to join Distinct values from the first table:
| EmpID | ComID |
| 101 | 1 |
| 102 | 2 |
| 103 | 3 |
| 101 | 4 |
With values from the second table:
| EmpRefNumber | FirstName | LastName |
| 105 | Ben | B |
| 101 | Ryan | G |
| 102 | James | H |
| 200 | Rose | F |
and keep only values from the first table (Left join).
The join should be based on EmpID and EmpRefNumber
Thank you!
Hi,
What result are you expecting?
Hi Ashish_Mathur,
Table1.EmpID Table2.EmpRefNumber Table2.FirstName Table2.LastName 101 101 Ryan G 102 102 James H 103 NULL NULL NULL With distinct values on Table1.EmpID column