cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brendenbunker Frequent Visitor
Frequent Visitor

Power Query equivalent to Left Join

I am working on a project for sales where there are 4 types of offers and every rep should be making each. I want to see how many of them are doing each kind of offer.

 

For my data, I have a query showing every offer made that contains the reps name and the offer type. Then I have a second query showing all the offer types.

 

What I would like is a new query that takes the list of offers and shows me how many of each type of offer each rep is making. I know I can do a group by Rep and offer then that will give me a count for all of them except if a rep has not made any of a singe offer.

 

What I was thinking to do is what in SQL would be a left join when for each rep it will add a row for each offer type. Then I could do a left outer join with a counts table (The counts table would be created by grouping the offer data by rep name then offer type) where it checks for matching rep name and offer type, then if it does not have a match, it would just put null in the new column.

 

I am sure there is a better way to do that but this is the only way I can think of. My problem is there is not Left Join in power query. Any recomendations for ways aroud this? Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Power Query equivalent to Left Join

@brendenbunker,

 

Just add a custom column.

= Table.AddColumn(Source, "Custom", each Table2)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Power Query equivalent to Left Join

Hi @brendenbunker

 

in Power Query you have the possibility to join two tables by using the 'Merge' feature. In there you can choose different kinds of joins, Left Outher, Inner, Right Outer, Left anti, Right Anti. Have you already tried to use it?

 


 


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


Proud to be a Datanaut!  

brendenbunker Frequent Visitor
Frequent Visitor

Re: Power Query equivalent to Left Join

Yes. The issue is for each rep, i need all all the contents of the second table.

Super User
Super User

Re: Power Query equivalent to Left Join

Hi,

 

Share a dataset and show the expected result.  Paste the data here in a format so that i can easily copy and paste it in an Excel workbook.

Community Support Team
Community Support Team

Re: Power Query equivalent to Left Join

@brendenbunker,

 

Just add a custom column.

= Table.AddColumn(Source, "Custom", each Table2)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.