cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Re: How to join a table with a subquery using power query M

Hi @LivioLanzo and @ImkeF, thanks for your help!

I want to join Distinct values from the first table:

EmpIDComID
1011
1022
1033
1014

 

With values from the second table:

EmpRefNumberFirstNameLastName
105BenB
101RyanG
102JamesH
200RoseF

 

and keep only values from the first table (Left join).

The join should be based on EmpID and EmpRefNumber

 

Thank you!

Highlighted
Super User III
Super User III

Re: How to join a table with a subquery using power query M

Hi,

 

What result are you expecting?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper I
Helper I

Re: How to join a table with a subquery using power query M

Hi @Ashish_Mathur,

 

Table1.EmpIDTable2.EmpRefNumberTable2.FirstNameTable2.LastName
101101RyanG
102102JamesH
103NULLNULLNULL

With distinct values on Table1.EmpID column

Highlighted
Solution Sage
Solution Sage

Re: How to join a table with a subquery using power query M

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
    ExpandedJoinedTable

 

Or in DAX:

 

Table =
NATURALLEFTOUTERJOIN (
    DISTINCT ( SELECTCOLUMNS ( Table1, "EmpID", [EmpID] + 0 ) ),
    SELECTCOLUMNS (
        Table2,
        "EmpID", Table2[EmpRefNumber] + 0,
        "FirstName", table2[FirstName],
        "LastName", table2[LastName]
    )
)

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Regular Visitor

Re: How to join a table with a subquery using power query M

Hey Greg,

 

How can we join via nestedjoin on multiple condition but with or conditrion.

 

Example: Select A.id ,A.Name B.Name,B.Course from A, B where (A.id=B.id Or A.Name = B.Name).

 

I was trying to do it using Merge query , I am able to select multiple column when joining from but A and B but I think it is taking And operator.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors