Power Query: Calculating Incentive Points using Advanced Group-by & Left Outer Join

/in /by

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: https://datascopic.net/buddies

#PowerQuery
#OuterJoin
#GroupBy

 For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2