cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: 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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Highlighted
Super User IX
Super User IX

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

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

14 REPLIES 14
Highlighted
Super User IX
Super User IX

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

Have you looked at Merge queries?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Helper I
Helper I

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

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

Highlighted
Super User IX
Super User IX

Re: 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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Highlighted
Helper I
Helper I

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

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?

Highlighted
Super User IX
Super User IX

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

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Highlighted
Helper I
Helper I

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

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!

Highlighted
Super User IX
Super User IX

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

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Solution Sage
Solution Sage

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

@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!  

Highlighted
Super User IV
Super User IV

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

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

Helpful resources

Announcements
Upcoming Events

Upcoming Events

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

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

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

Top Solution Authors