SOLVED

Power Query - Allocation Query

%3CLINGO-SUB%20id%3D%22lingo-sub-2729109%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Allocation%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2729109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ParasUdani_0-1631029898977.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308558i897A34A33813EB31%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ParasUdani_0-1631029898977.png%22%20alt%3D%22ParasUdani_0-1631029898977.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20one%20genuine%20issue%20and%20if%20someone%20can%20help%20to%20resolve%20through%20Power%20Query.%20I%20want%20to%20automate%20this%20task%20as%20its%20a%20regular%20activity.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1st%20Table%20on%20the%20left%20-%20Has%20total%20categories%20for%20the%20day.%3C%2FP%3E%3CP%3E2nd%20Table%20from%20the%20left%20(Middle)%20-%20Pre%20defined%20Team%20Members%20who%20will%20be%20responsible%20for%20respective%20category%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinal%20Output%20-%20Basically%20I%20need%20to%20equally%20allocate%20categories%20based%20on%20the%20Data%20from%20both%20the%20tables.%20But%20considering%20they%20are%20assigned%20equally%20based%20on%202nd%20table%20(Middle%20one).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2729109%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2730359%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Allocation%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2730359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1145786%22%20target%3D%22_blank%22%3E%40ParasUdani%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETable%20names%20as%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20478px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308733i056B8F3C6B8E0364%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESolution%20is%20done%20in%20few%20steps%20and%20some%20coding%20inside%20queries.%20Major%20steps%20are%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20564px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308735i9FDEE411A47DCD0E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20Category%20Count%20we%20count%20number%20of%20records%20for%20each%20category%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20361px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308736i415EB668E7B7D22C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETeam%20Count%20-%20number%20of%20available%20team%20members%20for%20each%20category%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20365px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308737i28A051DEADE51341%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20Group%20per%20Category%20we%20merge%20Group%20Count%20with%20Team%20Count%2C%20divide%20first%20count%20on%20second%20one%20and%20round%20result%20up%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20359px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308738i987E1E4CB992B61C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20Team%20With%20Groups%20we%20merge%20Team%20with%20Groups%20per%20Category%20and%20by%20few%20steps%20generate%20two%20indexes%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20681px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308742i470A04C56EF46BC5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIndex%20repeats%20team%20member%20number%20within%20category%2C%20Index2%20sequential%20number%20of%20generated%20records%20per%20each%20category.%20Since%20we%20rounded%20up%20Group%20per%20Category%2C%20here%20could%20be%20more%20records%20than%20in%20initial%20Category%20table.%3C%2FP%3E%0A%3CP%3EIn%20Category%20Indexes%20we%20sequentially%20number%20records%20for%20each%20category%20in%20Category%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20367px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308743i9C2BB6784DBC7ACE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFinally%20we%20merge%20Category%20Indexes%20with%20Team%20with%20Category%20and%20expand%20team%20members.%20That%20is%20Final%20table%20which%20is%20on%20first%20screenshot.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20explain%20detailed%20steps%20within%20each%20query%2C%20you%20may%20check%20them%20one%20by%20one%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2731118%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Allocation%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2731118%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much!%3C%2FLINGO-BODY%3E
Occasional Contributor

ParasUdani_0-1631029898977.png

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). 

3 Replies
best response confirmed by ParasUdani (Occasional Contributor)
Solution

@ParasUdani 

Table names as here

image.png

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

image.png

In Category Count we count number of records for each category

image.png

Team Count - number of available team members for each category

image.png

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

image.png

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

image.png

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

image.png

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