Power Query M Question - Time in Pod/training

[KCantor]

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

 

[Ken Puls]

Let's try this to get started:

1) Filter out the "End Date" records that don't equal 12/31/9999 (Based on your sample data, this gives you only people still in the pod)

2) To work out the InTraining status:

• Go to Add Column --> Add Custom Column
  • Name: Today's Date
  • Formula: =DateTime.LocalNow()
• Select the Today's Date column, hold down CTRL, select the First Training Date column
• Go to Add Column --> Date --> Subtract Days
• Go to add Column --> Add Custom Column
  • Name: InTraining
  • Formula:

if [DateDifference]<45then "In Training"
else null

• Select the Today's Date and DateDifference columns and remove them

To be fair, this could be done in less steps, but I wanted you to drive the user interface way to accomplish this.

Let's save your query as a connection:

• Change the Query name to "Base"
• Go to Home --> click the bottom of the Close & Load button --> Close & Load To... --> Only Create Connection

You now have a pointer to the data set with the current pod employees.

3) Let's work out how many people are in each pod

• In the Workbook Queries pane, right click the "Base" query --> Reference
• Filter the InTraining column --> uncheck the "In Training" value (this leaves only full fledged staff)
• Go to Transform --> Group By
  • Group By: TopPodName
  • New column name: CountOfPeople
  • Operation: Count Rows

This gives you a table showing only the count of the employees. (If you had sales data in that same table, we could have grouped it here too.)

Let's save your query as a connection:

• Change the Query name to "PeoplePerPod"
• Go to Home --> click the bottom of the Close & Load button --> Close & Load To... --> Only Create Connection

I know that doesn't get you to point 4 yet, and the data is not loading anywhere, but does this makes sense so far?

 

[KCantor]

Okay, I am with you and now have both references built in. First off, thank you very much for assisting me and second, WOW, I think this tool is going to become my favorite.
It is making perfect sense and bringing clarity to an entire plethora of questions. Please continue!

 

[Ken Puls]

Out of curiosity, are you loading this into Power Pivot, or just into regular Excel tables?

 

[KCantor]

I am loading this into PowerPivot and, eventually, PowerBI to create dashboards. The end goal is to tie sales to calls and reps.

 

[Ken Puls]

Okay, so here's the deal. What I figured is probably the best idea here is to add the Pod to the Sales Fact table, as the pod is time dependent. To do that, we create a custom function (chapter 21 of the book).

• Right click the Base query to create a referenced query
• Change the type of the Start Date column to Date (from DateTime)
• Sort the Start Date column in descending order (bringing the newest record to the top)
• Filter the SalesPerson column to anyone. (I used Castillo since this returns multiple results)
• Filter the Start Date column to Before or Equal To any date you choose
• Right click the Pod Name in the first row and choose Drill Down

We've now done the leg work for the query, we need to make it dynamic. Go into the Advanced Editor and make the following changes:

• Add the following line at the top:

(salesperson as text, saledate as date) =>

• Now modify the Filtered Rows lines so that our variables are in there:

    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([SalesPersonCode] = [COLOR=#ff0000]salesperson[/COLOR])),    
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Start Date] <= [COLOR=#ff0000]saledate[/COLOR]),

• Click Done
• Change the query name to fxGetPod

You're now set to use it...

• Create a new query that references the SalesPerformance table
• Go to Add Column --> Add Custom Column
  • Column Name: Pod
  • Formula: fxGetPod([SalesPerson],[OrderDate])

And that should do it.

 

[KCantor]

I will be trying to finish this today. My book is actually due in tomorrow so hopefully between this answer and the book I will get this working. If not, I will be back here with remarks. I didn't want you to think I didn't appreciate your help. Just taking me a little longer due to work and inexperience.