Forum Discussion
Help with successfully merging tables / lookup?
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!
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.
2 Replies
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.
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
