Forum Discussion

artfulmunkeey's avatar
artfulmunkeey
Helper I
6 years ago
Solved

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 so...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

BA_Pete's avatar
BA_Pete
Super User
6 years ago

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