Help calculating value from different tables with multiple criteria

[rmorris]

Hi, I'm having trouble adding a colum that will correctly return the values between these two tables. Effectivly, i need to create a column in table 1, that adds up the values of the TotalTimeHours in table 2. BUT, it must only add the values were both nkey and skey1 both match. (there are thousands of differnt nkey's in the table, and many will have multiple skey1.

Table 1

source_id	nkey	skey1	skey2	skey3	Table_Key	host_id
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	1	1	1	6424266	72439306
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	2	1	1	6424265	72439306
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	3	1	1	6424264	72439306
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	4	1	1	6424263	72439306
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	5	1	1	6424262	72439306
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	6	1	1	6424261	72439306
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	7	1	1	6424260	72439306
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	8	1	1	6424256	72439306
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	9	1	1	6424259	72439306

Table 2

source_id	nkey	skey1	Table_Key	host_id	database_id	TotalTimeHours
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	1	6424292	72439306	STORE412	0.05
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	2	6424295	72439306	STORE412	2.16
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	2	6424294	72439306	STORE412	0.84
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	4	6424293	72439306	STORE412	0.04
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	5	6424291	72439306	STORE412	0.05
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	5	6424289	72439306	STORE412	0.10
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	7	6424288	72439306	STORE412	0.10
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	7	6424287	72439306	STORE412	0.50
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	9	6424284	72439306	STORE412	0.58

End result i'm trying to achieve with Table 1, it needs to be able to idenify, that if there is no match for both nkey and skey1 in table 2 then return 0.00, otherwise show a summ of all values in table 2 that match both the nkey and skey.

source_id	nkey	skey1	skey2	skey3	Table_Key	host_id	TotalTimeHours
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	1	1	1	6424266	72439306	0.05
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	2	1	1	6424265	72439306	3.00
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	3	1	1	6424264	72439306	0.00
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	4	1	1	6424263	72439306	0.04
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	5	1	1	6424262	72439306	0.05
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	6	1	1	6424261	72439306	0.00
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	7	1	1	6424260	72439306	0.60
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	8	1	1	6424256	72439306	0.00
F7F8C66A4ABB44EAC8D145C1DA353FCE	M312538	9	1	1	6424259	72439306	0.58

[aditya0125 · Accepted solution]

Do one thing, create dummy key in both the tables and use merge query function in query editor to fetch the TotalTimeHours Column.

[wdx223_Daniel · Accepted solution]

NewStep=let a=Table.Buffer(Table.Group(Table2,{"source_id","nkey","skey1"},{"amt",each List.Sum([TotalTimeHours])})) in Table.AddColumn(Table1,"TotalTimeHours",each a{[source_id=[source_id],nkey=[nkey],skey1=[skey1]]}?[amt]? ??0)

[aditya0125 · Accepted solution]

Do one thing, create dummy key in both the tables and use merge query function in query editor to fetch the TotalTimeHours Column.