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 IV
Super User IV

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors