Merge data from 2 data sources in a Pivot Table to get a Consolidated Project view

Here’s a simple four column table showing date wise amount spent per project

Date	Project ID	Project Name	Total Amount
10-01-2015	P250	Project A	100
15-03-2015	P250	Project A	250
01-08-2015	P250	Project A	175
01-12-2015	P250	Project A	90
16-01-2016	P250	Project A	75
28-02-2016	P250	Project A	105
23-02-2016	P300	Project B	175
01-03-2016	P300	Project B	85
06-03-2016	P300	Project B	66
07-04-2016	P300	Project B	150
15-03-2016	P300	Project B	25
28-03-2016	P300	Project B	200
26-04-2016	P300	Project B	450

Here’s another table which shows project wise gates assigned within a time frame.

Project ID	Project Name	Project Gates	Beginning date	Ending date
P250	Project A	Release Gate	01-01-2015	31-05-2015
P250	Project A	Mid Gate	01-06-2015	30-12-2015
P250	Project A	Review Gate	31-12-2015	31-01-2016
P250	Project A	Final Gate	01-02-2016	29-02-2016
P300	Project B	Release Gate	15-02-2016	29-02-2016
P300	Project B	Mid Gate	01-03-2016	14-03-2016
P300	Project B	Review Gate	15-03-2016	14-04-2016
P300	Project B	Final Gate	15-04-2016	31-12-2016

The result expected is a Pivot Table which should mesh data from both datasets:

Project ID	Project Name	Date	Gates	Project Amount
P250	Project A	10-01-2015	Release Gate	100.00
		15-03-2015	Release Gate	250.00
		01-08-2015	Mid Gate	175.00
		01-12-2015	Mid Gate	90.00
		16-01-2016	Review Gate	75.00
		28-02-2016	Final Gate	105.00
P300	Project B	23-02-2016	Release Gate	175.00
		01-03-2016	Mid Gate	85.00
		06-03-2016	Mid Gate	66.00
		15-03-2016	Review Gate	25.00
		28-03-2016	Review Gate	200.00
		07-04-2016	Review Gate	150.00
		26-04-2016	Final Gate	450.00
Grand Total				1,946.00

While the 1st, 2nd, 3rd and 5th columns are easy to get in a Pivot Table from Table 1, the challenge is to get the “Gates” information from Table 2 inside the Pivot Table.

You may refer to my Power Query a.k.a Get & Transform (available under Data) in Excel 2016 and PowerPivot solution here.