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)
I believe the the equivalent M-syntax to the SQL-syntax you've mentioned in your original post would be this:
= Table.NestedJoin(Table1,,{"EmpID"},Table.Distinct(Table2, {"EmpID"}),{"EmpID"},"Table2",JoinKind.LeftOuter)
So Greg_Deckler syntax was correct, just that it referred to the wrong table, as far as I can see.
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
You can do it like this:
let Source = Table.NestedJoin( Table.Distinct( Table.SelectColumns( Table1, {"EmpID"} ) ), {"EmpID"}, Table2, {"EmpRefNumber"}, "JoinedTable", JoinKind.LeftOuter ), ExpandedJoinedTable = Table.ExpandTableColumn(Source, "JoinedTable", {"FirstName", "LastName"}, {"FirstName", "LastName"}) in ExpandedJoinedTableOr in DAX:
Table = NATURALLEFTOUTERJOIN ( DISTINCT ( SELECTCOLUMNS ( Table1, "EmpID", [EmpID] + 0 ) ), SELECTCOLUMNS ( Table2, "EmpID", Table2[EmpRefNumber] + 0, "FirstName", table2[FirstName], "LastName", table2[LastName] ) )
