Power Query: Calculating Incentive Points using Advanced Group-by & Left Outer Join A friend contacted me and asked how Excel might help calculate points for what he described as a "buddy system" where he goes to the gym. Here's the challenge:

- Marvin and Drew are paired in this buddy system
- If one attends a class, the team gets 1 point.
- If both attend together, the team gets 4 points.
-- How can we easily tally each team's points?

This video looks at 3 teams (6 people) and a list of classes. In order to solve this, we use Power Query's: group-by, advanced group-by, load to connection only, and a left outer join.

Download the workbook:



#PowerQuery
#OuterJoin
#GroupBy



 For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:








Website:



My book: Guerrilla Data Analysis 2nd Edition





My old blog:

Power Query,calculating incentives,power query outer join,column by example,Oz du Soleil,Excel on Fire,pivot table example in excel,refresh data in Excel,power query joins,example of power query join,group-by in power bi,group by in power query,grouping in power query,advanced grouping in excel,