Forum Discussion
Looping / Custom Function Question
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
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
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
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
