Forum Discussion
Issue matching (dates) MS Project Capacity & demand to calculate utilisation
Hi all, I am relatively new to Power Queries and have been learning through trial and error.
I have one particular issue which I have been struggling with for some time and various attempts to solve it leave me even more confused.
I have two datasets (ODATA) from MS Project - ResourceDemandTimePhased (Demand by Resource and Project - per month) & ResourceTimePhased (Capacity by Resource - per day).
They resemble the below tables.
| ResourceDemandTimePhased | |||
| Proejct ID | Resource ID | Date | Demand |
| Project 1 | Resource 1 | Jan-20 | 100 |
| Project 1 | Resource 1 | Feb-20 | 80 |
| Project 1 | Resource 1 | Mar-20 | 120 |
| Project 1 | Resource 2 | Feb-20 | 100 |
| Project 1 | Resource 2 | Mar-20 | 80 |
| Project 1 | Resource 3 | Jan-20 | 100 |
| Project 2 | Resource 1 | Feb-20 | 20 |
| Project 2 | Resource 1 | Mar-20 | 30 |
| Project 2 | Resource 2 | Mar-20 | 20 |
| Project 2 | Resource 2 | Apr-20 | 50 |
| Proejct 2 | Resource 3 | Feb-20 | 100 |
| ResourceTimePhased | ||
| Resource ID | Date | Capacity |
| Resource 1 | 01/01/2020 | 8 |
| Resource 1 | 02/01/2020 | 8 |
| Resource 1 | 03/01/2020 | 8 |
| Resource 1 | 04/01/2020 | 8 |
| Resource 2 | 01/01/2020 | 8 |
| Resource 2 | 02/01/2020 | 8 |
| Resource 2 | 03/01/2020 | 8 |
| Resource 2 | 04/01/2020 | 8 |
| Resource 3 | 01/01/2020 | 8 |
| Resource 3 | 02/01/2020 | 8 |
| Resource 3 | 03/01/2020 | 8 |
| Resource 3 | 04/01/2020 | 8 |
I want to calcualte a percentage utilisation (Demand/Capacity) for each Resource per month and be able to filter by project, however, I cannot match the data.
I have attempted to merge the two, datasets, but cannot do it correctly and end up with the wrong values as I either end up with too many days, or duplicate entries for projects.
I believe I need to somehow sum the capacity monthly whilst grouping by Resource, before merging the two sets to add a measure to calculate the percentage. But struggling to do this and understand how to retain the Project filter.
I cannot get my head around it.
Can anyone please advise?
Thanks in advance!
Hello artfulmunkeey
here another approach.
Check it out
let Demand = let Source = #table ( {"Proejct ID","Resource ID","Date","Demand"}, { {"Project 1","Resource 1","Jan-20","100"}, {"Project 1","Resource 1","Feb-20","80"}, {"Project 1","Resource 1","Mar-20","120"}, {"Project 1","Resource 2","Feb-20","100"}, {"Project 1","Resource 2","Mar-20","80"}, {"Project 1","Resource 3","Jan-20","100"}, {"Project 2","Resource 1","Feb-20","20"}, {"Project 2","Resource 1","Mar-20","30"}, {"Project 2","Resource 2","Mar-20","20"}, {"Project 2","Resource 2","Apr-20","50"}, {"Proejct 2","Resource 3","Feb-20","100"} } ), changetype = Table.TransformColumnTypes(Source, {{"Demand", Int64.Type}}), AddMonthYear = Table.AddColumn(changetype, "MonthYear", each Text.From(Date.Month(Date.From([Date]))) & "-" & Text.From(Date.Year(Date.From([Date])) )) in AddMonthYear, Capacity = let Source = #table ( {"Resource ID","Date","Capacity"}, { {"Resource 1","01/01/2020","8"}, {"Resource 1","02/01/2020","8"}, {"Resource 1","03/01/2020","8"}, {"Resource 1","04/01/2020","8"}, {"Resource 2","01/01/2020","8"}, {"Resource 2","02/01/2020","8"}, {"Resource 2","03/01/2020","8"}, {"Resource 2","04/01/2020","8"}, {"Resource 3","01/01/2020","8"}, {"Resource 3","02/01/2020","8"}, {"Resource 3","03/01/2020","8"}, {"Resource 3","04/01/2020","8"} } ), changetype = Table.TransformColumnTypes(Source, {{"Capacity", Int64.Type}}), AddMonthYear = Table.AddColumn(changetype, "MonthYear", each Text.From(Date.Month(Date.From([Date]))) & "-" & Text.From(Date.Year(Date.From([Date])) )) in AddMonthYear, Join = Table.NestedJoin(Demand, {"Resource ID", "MonthYear"}, Capacity, {"Resource ID", "MonthYear"}, "Capacity"), #"Aggregated Capacity" = Table.AggregateTableColumn(Join, "Capacity", {{"Capacity", List.Sum, "Sum of Capacity"}}) in #"Aggregated Capacity"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
2 Replies
Hi artfulmunkeey ,
Try this and see if it does what you want:
Go to New Source>Blank Query, then in Advanced Editor paste my code over the default code for each table below. Take note of the names you need to call them commented in the first row of each:
//Call this query ResourceTimePhased let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotzi8tSk5VMFTSUTIw1AciIwMjAyDHQilWB13eiIC8MQF5E9zyRgTsNyJgvxEB+40I2G9MwH5jAvYbE7DfGIv9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Resource ID" = _t, Date = _t, Capacity = _t]), chgAllTypes = Table.TransformColumnTypes(Source,{{"Resource ID", type text}, {"Date", type date}, {"Capacity", Int64.Type}}), addStartOfMonth = Table.AddColumn(chgAllTypes, "StartOfMonth", each Date.StartOfMonth([Date]), type date), #"groupResource,SoM" = Table.Group(addStartOfMonth, {"Resource ID", "StartOfMonth"}, {{"Capacity", each List.Sum([Capacity]), type number}}) in #"groupResource,SoM"//Call this query ResourceDemandTimePhased let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRCkotzi8tSk4Fc7wS83SNDIAMQwMDpVgdPCrdUpMgKi0IKPRNLIIaaYRHpRGykXgtN0I2E5/lxvj8Y4TTP0YEFMLtNsajEMWR+EwEcRwLoApN4QpTs9AVGmMEUCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [projectID = _t, resourceID = _t, Date = _t, Demand = _t]), chgAllTypes = Table.TransformColumnTypes(Source,{{"projectID", type text}, {"resourceID", type text}, {"Date", type date}, {"Demand", Int64.Type}}), mergeResourceTimePhased = Table.NestedJoin(chgAllTypes, {"resourceID", "Date"}, ResourceTimePhased, {"Resource ID", "StartOfMonth"}, "ResourceTimePhased", JoinKind.LeftOuter), expandResourceTimePhased = Table.ExpandTableColumn(mergeResourceTimePhased, "ResourceTimePhased", {"Capacity"}, {"Capacity"}) in expandResourceTimePhasedYou should now be able to follow the steps I took to merge these together. Summary:
1) Added [StartOfMonth] column to ResourceTimePhased, then grouped on this and [Resource ID] with sum of Capacity.
2) Merged ResourceDemandTimePhased [Resource ID] & [Date] with ResourceTimePhased [Resource ID] & [StartOfMonth]
This gives me the following output:
*NB* the ResourceTimePhased query has no awareness of Project, so capacity is just applied in its total to each [Resource ID] in the merged query, regardless of the project.
Pete
Hello artfulmunkeey
here another approach.
Check it out
let Demand = let Source = #table ( {"Proejct ID","Resource ID","Date","Demand"}, { {"Project 1","Resource 1","Jan-20","100"}, {"Project 1","Resource 1","Feb-20","80"}, {"Project 1","Resource 1","Mar-20","120"}, {"Project 1","Resource 2","Feb-20","100"}, {"Project 1","Resource 2","Mar-20","80"}, {"Project 1","Resource 3","Jan-20","100"}, {"Project 2","Resource 1","Feb-20","20"}, {"Project 2","Resource 1","Mar-20","30"}, {"Project 2","Resource 2","Mar-20","20"}, {"Project 2","Resource 2","Apr-20","50"}, {"Proejct 2","Resource 3","Feb-20","100"} } ), changetype = Table.TransformColumnTypes(Source, {{"Demand", Int64.Type}}), AddMonthYear = Table.AddColumn(changetype, "MonthYear", each Text.From(Date.Month(Date.From([Date]))) & "-" & Text.From(Date.Year(Date.From([Date])) )) in AddMonthYear, Capacity = let Source = #table ( {"Resource ID","Date","Capacity"}, { {"Resource 1","01/01/2020","8"}, {"Resource 1","02/01/2020","8"}, {"Resource 1","03/01/2020","8"}, {"Resource 1","04/01/2020","8"}, {"Resource 2","01/01/2020","8"}, {"Resource 2","02/01/2020","8"}, {"Resource 2","03/01/2020","8"}, {"Resource 2","04/01/2020","8"}, {"Resource 3","01/01/2020","8"}, {"Resource 3","02/01/2020","8"}, {"Resource 3","03/01/2020","8"}, {"Resource 3","04/01/2020","8"} } ), changetype = Table.TransformColumnTypes(Source, {{"Capacity", Int64.Type}}), AddMonthYear = Table.AddColumn(changetype, "MonthYear", each Text.From(Date.Month(Date.From([Date]))) & "-" & Text.From(Date.Year(Date.From([Date])) )) in AddMonthYear, Join = Table.NestedJoin(Demand, {"Resource ID", "MonthYear"}, Capacity, {"Resource ID", "MonthYear"}, "Capacity"), #"Aggregated Capacity" = Table.AggregateTableColumn(Join, "Capacity", {{"Capacity", List.Sum, "Sum of Capacity"}}) in #"Aggregated Capacity"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
