Help with successfully merging tables / lookup?

[artfulmunkeey]

Hi, I am struggling with the following and wonder if someone can enlighten me as to what i am doing wrong?

I have two tables loaded.

Table 1 - ResourceDemandTimePhased has (in addition to others) the following columns

ResourceDemandTimePhased
ProjectName	ResourceName	Date	Demand
Project1	Dave	01/01/2020	150
Project1	Dave	01/02/2020	129
Project1	Dave	01/03/2020	100
Project1	Dave	01/04/2020	160
Project1	Bob	01/01/2020	120
Project1	Bob	01/02/2020	140
Project1	Bob	01/05/2020	150
Project2	Dave	01/01/2020	100
Project2	Dave	01/02/2020	80
Project2	Dave	01/03/2020	60
Project2	Dave	01/04/2020	90
Project2	Bob	01/03/2020	100
Project2	Bob	01/04/2020	120
Project2	Bob	01/05/2020	110
Project2	Bob	01/06/2020	100

Table 2 - TimeSheets has (in addition to others) the following columns

Timesheets
ProjectName	ResourceName	Date	Actual
Project1	Dave	01/02/2020	20
Project1	Dave	01/03/2020	30
Project1	Dave	01/04/2020	50
Project1	Dave	01/05/2020	80
Project1	Bob	01/01/2020	100
Project1	Bob	01/02/2020	120
Project1	Bob	01/05/2020	100
Project2	Dave	01/01/2020	80
Project2	Dave	01/02/2020	60
Project2	Dave	01/03/2020	50
Project2	Dave	01/04/2020	80
Project5	Bob	01/03/2020	90
Project5	Bob	01/04/2020	110
Project5	Bob	01/05/2020	80
Project5	Bob	01/06/2020	60

I wish to create the following outcome. Table 2 doesnt contain nearly as many rows as table 1 (Timesheets are only for a subset of all projects). I do not wish to lose the rows in Table 1 which do not match (Ideally I want to import the Actual hours, matching them to project ID, resource ID and date in Table 1). I have attempted to merge, group and write a lookup function all without success.

Desired Output
ProjectName	ResourceName	Date	Demand	Actual	Remaining
Project1	Dave	01/01/2020	150	0	150
Project1	Dave	01/02/2020	129	20	109
Project1	Dave	01/03/2020	100	30	70
Project1	Dave	01/04/2020	160	50	110
Project1	Bob	01/01/2020	120	100	20
Project1	Bob	01/02/2020	140	120	20
Project1	Bob	01/05/2020	150	100	50
Project2	Dave	01/01/2020	100	80	20
Project2	Dave	01/02/2020	80	60	20
Project2	Dave	01/03/2020	60	50	10
Project2	Dave	01/04/2020	90	80	10
Project2	Bob	01/03/2020	100	90	10
Project2	Bob	01/04/2020	120	110	10
Project2	Bob	01/05/2020	110	80	30
Project2	Bob	01/06/2020	100	60	40

Any help would be greatly appreciated, thanks!

[Greg_Deckler · Accepted solution]

@artfulmunkeey - Seems like you could create a composite key in both tables and do a merge in Power Query and that might make things very easy.

[artfulmunkeey]

Thanks for the fast response and for pointing me down the right track. That worked, however, it has opened another can of worms and I now have a follow-up question if I may..

I added a table of resource names and IDs, a separate one for project names and IDs, and a third table as a timeset for every day of the year (dd/mm/yyyy). I've created all the necessary relatioshships and can now produce the following pivot table which can be drilled down to show per resource below each project.

	Jan	Feb	Mar
ProjectName	Demand	Actual	Demand	Actual	Demand	Actual
Project 1	500.0	200.0	300.0	140.0	100.0	120.0
Project 2	700.0	950.0	750.0	900.0	750.0	150.0
Project 3			50.0	20.0	90.0	90.0
Project 4					100.0	110.0
Project 5	100.0	90.0	90.0	100.0	100.0	90.0
Project 6			50.0	40.0	80.0	70.0

However, as it is a pivot table with an OData feed i cannot create a calculated field to produce the following desired output. I suspect i need to write a query to do this but do not know where to begin grouping the data by project (and by resources beneath projects) by month, to produce the burn-down of available hours.

	Jan	Feb	Mar	Cumulative	TOTAL Demand
ProjectName	Cumulative	Demand	Actual	Remaining	Cumulative	Demand	Actual	Remaining	Cumulative	Demand	Actual	Remaining
Project 1	900.0	500.0	200.0	300.0	700.0	300.0	140.0	160.0	560.0	100.0	120.0	-20.0	440.0	900.0
Resource 1	350.0	200.0	100.0	100.0	250.0	100.0	40.0	60.0	210.0	50.0	50.0	0.0	160.0	350.0
Resource 2	550.0	300.0	100.0	200.0	450.0	200.0	100.0	100.0	350.0	50.0	70.0	-20.0	280.0	550.0
Project 2	2200.0	700.0	950.0	-250.0	1250.0	750.0	900.0	-150.0	350.0	750.0	150.0	600.0	200.0	2200.0
Project 3	140.0			0.0	140.0	50.0	20.0	30.0	120.0	90.0	90.0	0.0	30.0	140.0
Project 4	100.0			0.0	100.0			0.0	100.0	100.0	110.0	-10.0	-10.0	100.0
Project 5	290.0	100.0	90.0	10.0	200.0	90.0	100.0	-10.0	100.0	100.0	90.0	10.0	10.0	290.0
Project 6	130.0			0.0	130.0	50.0	40.0	10.0	90.0	80.0	70.0	10.0	20.0	130.0

Any suggestions on this would be very welcome!

Thanks