Power Query equivalent to Left Join

[Anonymous]

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!

[Ashish_Mathur]

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.

[LivioLanzo]

Hi @Anonymous

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?

[Anonymous]

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

[v-chuncz-msft · Accepted solution]

@Anonymous,

Just add a custom column.

= Table.AddColumn(Source, "Custom", each Table2)