Hello,
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).
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.
Accepted Solutions
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.