Looping / Custom Function Question

[ScottA]

I have a table of employees that need to complete a course. There are two different ranks of employees, “C” and “F”. They must each complete the course in the window between their ‘target date’ and ‘expiry date’. The list is ordered in ascending order based on expiry date i.e. those who expire soonest are the top priority to complete the course.

Employee Table

Employee #	Rank	Target Date	Expiry Date
1	C	13/01/2022	10/03/2022
2	C	18/01/2022	15/03/2022
3	C	18/01/2022	15/03/2022
4	C	18/01/2022	15/03/2022
5	F	18/01/2022	15/03/2022
6	F	18/01/2022	15/03/2022
7	C	18/01/2022	15/03/2022
8	F	1/02/2022	29/03/2022
9	C	15/02/2022	12/04/2022
10	F	4/03/2022	29/04/2022
11	F	5/03/2022	30/04/2022
12	F	15/03/2022	10/05/2022
13	C	14/04/2022	9/06/2022
14	C	11/05/2022	6/07/2022
15	F	15/05/2022	10/07/2022
16	C	9/06/2022	4/08/2022

Separately I have a table of the planned course dates, and the capacity on that course for each rank (“C” and “F”).

Course Table

Course	C capacity	F capacity
19/01/2022	3	2
16/02/2022	3	2
16/03/2022	3	2
13/04/2022	3	2
11/05/2022	3	2
8/06/2022	3	2

What I would like to do is loop through the table of employees, and assign course dates to employees from the possible options in the course table. The course assigned must sit within the target to expiry window, and we cannot exceed the capacity on any given course. To do this I imagine we need to (re)calculate assigned vs capacity at each step to determine if there are any more people we can put on the course, or we need to move to the next date.

For example;

Employees 1, 2, and 3 (all of rank “C”) can go on the 19/01/22 course. Employee 4 however needs to go on the 16/02/22 course, as the 19/01/22 course is now full for rank “C” (though can still be utilised for “F” employees), etc etc etc.

The output I would like is a column with courses assigned. There needs to be some redundancy built in if there are no possible options (as in the case of employee 16). I would like to achieve this with M language (power query), not DAX.

I think this can be achieved and have watched some videos on looping and custom functions but cannot think how to apply it in this sense. Very much appreciate any help.

Cheers

Ideal Output

Employee #	Rank	Target Date	Expiry Date	**Output
1	C	13/01/2022	10/03/2022	19-Jan
2	C	18/01/2022	15/03/2022	19-Jan
3	C	18/01/2022	15/03/2022	19-Jan
4	C	18/01/2022	15/03/2022	16-Feb
5	F	18/01/2022	15/03/2022	19-Jan
6	F	18/01/2022	15/03/2022	19-Jan
7	C	18/01/2022	15/03/2022	16-Feb
8	F	1/02/2022	29/03/2022	16-Feb
9	C	15/02/2022	12/04/2022	16-Feb
10	F	4/03/2022	29/04/2022	16-Mar
11	F	5/03/2022	30/04/2022	16-Mar
12	F	15/03/2022	10/05/2022	13-Apr
13	C	14/04/2022	9/06/2022	11-May
14	C	11/05/2022	6/07/2022	11-May
15	F	15/05/2022	10/07/2022	8-Jun
16	C	9/06/2022	4/08/2022	-

[mahoneypat · Accepted solution]

This is a challenging one. I didn't have time to finish it, but the attached pbix shows one way to approach this with List.Accumulate. It iterates over the list of Course dates and generates the first X Employees for which the Course date is between the Target and Expiry dates. Those Employees are removed from the list and the remaining employees are passed to the next iteration, and so on. This generates the list of "C" employees and you could adapt same approach for the "F" employees. From there you can merge the Employee table with this resulting table to get your desired result.

Note that Employee 16 didn't have a fit for any of the Course dates. Also, this approach is not very performant, so it would be slow if your tables are large.

Pat

[ScottA]

Hi @mahoneypat ,

Thanks very much for the time you put in to this. My delay in response has been trying to figure out a) what you have done and b) how to make it faster.

When transcribing the solution code to my real data I ran into a severe performance problem. The query just spun and sucked up CPU to the point after waiting 90 minutes it had only loaded 10 rows and I never could wait long enough to actually see the solution load. 

In the real world I have ~150 employees of each rank, and 18 months worth of courses averaging about 1.5 courses per week. So definitely larger than the example, but actually not very large in the scheme of things. 

By adding in extra buffers (Table.Buffer, List.Buffer) and then using them at each step I have solved this issue and its now very quick. The additions I have made to your code are shown below in yellow.

Thanks Again

[mahoneypat]

Glad you got it working faster. I had also tried it with List.Generate and it was similarly slow. I tested your change on my end. All I had to do was buffer the emp2 table, and it was already very quick. That makes sense, since it is the emp2 table that references the other query (i.e., I should have thought to do that).

Now you could improve the code further to accept a Rank value as a parameter too, so it would work for any course (and the code could be streamlined/formatted to look better).

Pat