I have ordered the book on M but still haven’t learned enough to be of any use. So, here I am again on the board asking for help making powerquery do my bidding.
I have a ‘Pod Members” table that I need to create a couple of calculations on. It has true duplicate entries that I need to remove in order to make the pivot table work but I don't know how to automatically judge which entry to keep. I also need to calculate days in pod, days in training, and, using that calculation create pod memberships.
I have attached a modified version of the table here. The names have been changed to protect the innocent.
Steps I need to create:
1.) Determine current pod membership. If a team member is currently in the pod, the end date will be 12/31/9999. Remove/hide duplicate member names that have left a pod. In the attached table there should be 3 examples of those duplicates. Of course the next step may need part of the information from the deleted entries.
2.) Determine if the member is in training. A member is in training if their start date is less than 45 days from the current date. After 45 days, they are no longer in training and count in calculations as a pod member. MSTARTED is not a new hire/ trainee but has switched to a new pod in the example.
3.) Create a method of calculating pod current pod membership to be used as a divisor in sales calculations. I need to be able to divide sales calls and sales dollars by the number of current pod members who are out of training. So, if a member is new, their calls and sales dollars count but the divisor is different. For example, JLENON is in the Sales Ninjas pod but has not been with us for 45 days. Therefore, the Sales Ninjas pod membership count should be 2, not 3 until his training is complete.
4.) As a wish list item, I would like to add a new column calculating the number of days spent in the new pod in case someone switched pods in the middle of a calculation period. My fear on this is how to keep them in two pods without creating duplicate entries that would negate table relationships. As a way to split sales calls and sales dollars between two pods if they changed to another.
As always, I appreciate your help and the sharing of your wisdom.
| SalesPersonCode | First Training Date | TopPodCode | Start Date | End Date | TopPodName | id |
| ALINCOLN | 9/23/2014 0:00 | TT | 1/1/2015 0:00 | 12/31/9999 0:00 | Team Troll | 99 |
| BCAUGHT | 10/27/2015 0:00 | BP | 2/6/2016 0:00 | 12/31/9999 0:00 | Bro Pod | 57 |
| BCAUGHT | 10/27/2015 0:00 | SJ | 10/27/2015 0:00 | 2/5/2016 0:00 | Sales Jedis | 58 |
| FPAPER | 11/10/2015 0:00 | SJ | 11/10/2015 0:00 | 2/22/2016 0:00 | Sales Jedis | 108 |
| GWASHINGTON | 1/5/2016 0:00 | BP | 1/5/2016 0:00 | 12/31/9999 0:00 | Bro Pod | 109 |
| JBEIBER | 2/17/2016 0:00 | SJ | 2/17/2016 0:00 | 12/31/9999 0:00 | Sales Jedis | 261 |
| JCASTILLION | 4/15/2014 0:00 | BP | 1/1/2015 0:00 | 1/12/2016 0:00 | Bro Pod | 317 |
| JCASTILLION | 4/15/2014 0:00 | DP | 1/13/2016 0:00 | 12/31/9999 0:00 | Digital Pod | 318 |
| JLENON | 2/25/2016 0:00 | SN | 2/25/2016 0:00 | 12/31/9999 0:00 | Sales Ninjas | 86 |
| JOCEAN | 6/30/2015 0:00 | TT | 6/30/2015 0:00 | 12/31/9999 0:00 | Team Troll | 141 |
| KPEAN | 1/6/2011 0:00 | SN | 1/1/2015 0:00 | 12/31/9999 0:00 | Sales Ninjas | 56 |
| MSTARTED | 8/13/2013 0:00 | BC | 1/1/2015 0:00 | 4/1/2016 0:00 | Big Cheese | 205 |
| MSTARTED | 8/13/2013 0:00 | DP | 4/2/2016 0:00 | 12/31/9999 0:00 | Digital Pod | 206 |
| NFASHION | 5/6/2013 0:00 | TT | 1/1/2015 0:00 | 12/31/9999 0:00 | Team Troll | 222 |
| OOSBORNE | 9/8/2015 0:00 | BC | 9/8/2015 0:00 | 12/31/9999 0:00 | Big Cheese | 245 |
| PFRAMPTON | 9/28/2010 0:00 | BC | 1/1/2015 0:00 | 12/31/9999 0:00 | Big Cheese | 61 |
| PMCCARTNEY | 8/11/2015 0:00 | SN | 8/11/2015 0:00 | 12/31/9999 0:00 | Sales Ninjas | 81 |
| STYLER | 3/18/2014 0:00 | BP | 1/1/2015 0:00 | 12/31/9999 0:00 | Bro Pod | 78 |
| TWEET | 5/27/2014 0:00 | BC | 1/1/2015 0:00 | 9/7/2015 0:00 | Big Cheese | 305 |
| TWEET | 5/27/2014 0:00 | SJ | 9/8/2015 0:00 | 12/31/9999 0:00 | Sales Jedis | 306 |
Did I answer your question? Mark my post as a solution!Proud to be a Super User!
