My master table contains below data
| RESOURCE NAME | SKILL GROUP | PROJECT | COST PER HOUR | CAPACITY |
|---|---|---|---|---|
| Resource 1 | Automation Testing | Project 1 | 12.0 | 800.0 |
| Resource 2 | DB Testing | Project 1 | 11.0 | 900.0 |
| Resource 3 | DB Testing | Project 1 | 12.0 | 800.0 |
| Resource 4 | Report Testing | Project 2 | 12.0 | 200.0 |
| Resource 5 | CICD and Devops | Project 3 | 11.0 | 800.0 |
| Resource 6 | Performance Testing | Project 1 | 12.0 | 900.0 |
| Resource 7 | Automation Testing | Project 2 | 10.0 | 250.0 |
| Resource 8 | Cloud Testing | Project 3 | 12.0 | 900.0 |
| Resource 9 | Report Testing | Project 1 | 11.0 | 800.0 |
| Resource 10 | Cloud Testing | Project 1 | 11.0 | 900.0 |
| Resource 11 | Report Testing | Project 3 | 12.0 | 800.0 |
and my references table has got these entries
| RESOURCE NAME | SKILL GROUP | PROJECT | DEMAND | Capacity |
|---|---|---|---|---|
| Resource 1 | Automation Testing | Project 2 | 900.0 | |
| Resource 2 | DB Testing | Project 1 | 300.0 | |
| Resource 3 | DB Testing | Project 1 | 400.0 | |
| Resource 1 | Report Testing | Project 1 | 200.0 | |
| Resource 4 | CICD and Devops | Project 3 | 300.0 | |
| Resource 5 | Performance Testing | Project 2 | 900.0 | |
| Resource 6 | Automation Testing | Project 1 | 200.0 | |
| Resource 2 | Cloud Testing | Project 2 | 900.0 | |
| Resource 7 | Report Testing | Project 1 | 400.0 | |
| Resource 8 | Cloud Testing | Project 3 | 800.0 | |
| Resource 9 | Report Testing | Project 2 | 900.0 | |
| Resource 10 | Pipeline Testing | Project 1 | 600.0 | |
| Resource 11 | Cloud Testing | Project 3 | 700.0 | |
| Resource 10 | Performance Testing | Project 2 | 900.0 | |
| Resource 11 | Automation Testing | Project 1 | 250.0 |
What I am trying to achieve here to distribute my capacity hours given in the master table to all the matching records in reference table.
For example: In master table for Resource 10 total capacity is 900 hours and he has been allocated to 2 different projects in reference table, so his capacity columns in the reference table will be updated and total capacity should be distributed to 450 each and in future if he get assigned more projects then his total capacity will be distributed among all the entries matches to Resource 10. Same for other matching Resources.
CodePudding user response：
See if this works for you, in powerquery
Load Master table as a query. File .. close and load to ... create connection only. Name it MasterTable
Load Reference table (without the capacity column) into powerquery
Right click Resource Name, group by, [x] advanced
For the first row, use operation count rows
For the second row, use operation all rows
Expand the data using the arrows atop the new column that is created
Merge the MasterTable into this one, left outer join, using the Resource Name column to match
Expand the capacity column using the arrows atop the new column that is created
Add column... custom column .. that divides capacity by count. Remove extra columns
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RESOURCE NAME", type text}, {"SKILL GROUP", type text}, {"PROJECT", type text}, {"DEMAND", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"RESOURCE NAME"}, {{"Count", each Table.RowCount(_), type number}, {"Data", each _, type table}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"SKILL GROUP", "PROJECT", "DEMAND"}, {"SKILL GROUP", "PROJECT", "DEMAND"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Data",{"RESOURCE NAME"},MasterTable,{"RESOURCE NAME"},"MasterTable",JoinKind.LeftOuter),
#"Expanded MasterTable" = Table.ExpandTableColumn(#"Merged Queries", "MasterTable", {"CAPACITY"}, {"FULLCAPACITY"}),
#"Added Custom" = Table.AddColumn(#"Expanded MasterTable", "Capacity", each [FULLCAPACITY]/[Count]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "FULLCAPACITY"})
in #"Removed Columns"
output from slightly different input to show decimals
CodePudding user response：
In Excel 365:
=XLOOKUP([@[Resource Name]],MasterTable[Resource Name],MasterTable[Capacity])/COUNTIF([Resource Name],[@[Resource Name]])
for the first few entries.
I'm not sure if this is really what you want, but this is an example where the last entry is adjusted to make all entries for the same resource exact to one decimal place
=LET(totcap,XLOOKUP([@[Resource Name]],MasterTable[Resource Name],MasterTable[Capacity]),
count,COUNTIF([Resource Name],[@[Resource Name]]),
roundcap,ROUND(totcap/count,1),
IF(ROW()<MAX(IF([@[Resource Name]]=[Resource Name],ROW([Resource Name]))),roundcap,totcap-roundcap*(count-1)))
