Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
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
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
expandResourceTimePhased
You 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
Proud to be a Datanaut!