Power Query - Allocation Query

Occasional Contributor




I have one genuine issue and if someone can help to resolve through Power Query. I want to automate this task as its a regular activity. 


1st Table on the left - Has total categories for the day.

2nd Table from the left (Middle) - Pre defined Team Members who will be responsible for respective category


Final Output - Basically I need to equally allocate categories based on the Data from both the tables. But considering they are assigned equally based on 2nd table (Middle one). 

3 Replies
best response confirmed by ParasUdani (Occasional Contributor)


Table names as here


Solution is done in few steps and some coding inside queries. Major steps are


In Category Count we count number of records for each category


Team Count - number of available team members for each category


In Group per Category we merge Group Count with Team Count, divide first count on second one and round result up


In Team With Groups we merge Team with Groups per Category and by few steps generate two indexes


Index repeats team member number within category, Index2 sequential number of generated records per each category. Since we rounded up Group per Category, here could be more records than in initial Category table.

In Category Indexes we sequentially number records for each category in Category table


Finally we merge Category Indexes with Team with Category and expand team members. That is Final table which is on first screenshot.


I didn't explain detailed steps within each query, you may check them one by one in attached file.

Thank you so much!

@ParasUdani , you are welcome