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
kazael
Helper I
Helper I

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

Hi,

I have two tables that I'd like to merge:

Table 1: Complaints, employee ID

Table 2: Employee ID, Emp first name, Emp last name

I'd like to join table1 with a subquery of table2 with power query interface in PBI.

The reason I want to do it is first, get the details of employees who appear on the first table only (Table2 includes all the emp) and to get a table with distinct values (each emp will appear once) in order to reduce the size of the final table created from the merge.

My SQL query would look like this:

SELECT *

FROM Table1  Right outer join (SELECT DISTINCT EmpID FROM Table2)

On Table1 .EmpID = Table2 .EmpID

 

 

Thank you!

 

 

2 ACCEPTED SOLUTIONS

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

14 REPLIES 14
Greg_Deckler
Super User
Super User

Have you looked at Merge queries?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Yes, I merged them but it doesn't allow you to merge a table to a subquery of another table. 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler

Thank you for your help.

I know it is possible, I just don't know how to include the Distinct part in the query.

This is the query I use now to join the tables:

= Table.NestedJoin(Table1,{"EmpID"},Table2,{"EmpID"},"Table2",JoinKind.LeftOuter)

 

How would you modify the query to include only Distinct EmpID from Table1?

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Hi @Greg_Deckler,

It doesn't really work so I'll try to write the full M code using the article you attached.

 

Thank you!

@kazael

 

could you provide a visual example on which we could work?

 

 

 


 


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


Proud to be a Datanaut!  

OK, @ImkeF might be able to help as well, she is an M guru.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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!

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!  

Hi,

 

What result are you expecting?


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

Hi @Ashish_Mathur,

 

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

With distinct values on Table1.EmpID column

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.