Create calculated column on a table based on columns from two different other tables

[jessicarocha]

I would like to create a column in one table that is based in two other tables. The desired column (placed in the UNION table) should be able to say the amount of customer order schedule lines and purchee order schedule lines (CUSTO and PURCO).

Customer order schedule line table (CUSTO): 

Doc_nr		Item_nr		Schedule_line_nr		Order_qty		Confirmed_qty		Delivery_date
0002658769		0010		001		10		10		01.04.24
0002658769		0020		001		50		45		01.04.24
0002658769		0020		002		0		5		05.04.24
0002185398		0010		001		100		60		10.04.24
0002185398		0010		002		0		20		14.04.24
0002185398		0010		003		0		20		20.04.24

Purchase order schedule line (PURCO): 

Doc_nr		Item_nr		Schedule_line_nr		Order_qty		Confirmed_qty		Delivery_date
0004598731		0010		001		30		0		04.04.24
0004598731		0010		002		0		30		11.04.24
0004592147		0010		001		45		45		05.04.24

Header of the documents (UNION):

Doc_nr		Item_nr		Order_qty		Conf_qty		Source_table		Desired column (Schedule line amount)
0002658769		0010		10		10		CUSTO		1
0002658769		0020		50		50		CUSTO		2
0002185398		0010		100		100		CUSTO		3
0004598731		0010		30		30		PURCO		2
0004592147		0010		45		45		PURCO		1

Relationships: 

- PURCO n:1 UNION

- CUSTO n:1 UNION

I tried formulas for a calculated column with SUMMARIZE or LOOKUPVALUE but it is not working. 

One example of what I tried: 

TEST = 
IF(
   UNION[Source_table) = "PURCO", 
   SUMMARIZE(PURCO, PURCO[Doc_nr], PURCO[Item_nr], "amount sched. line", COUNT(PURCO[Schedule_line_nr]), 
   SUMMARIZE(CUSTO, CUSTO[Doc_nr], CUSTO[Item_nr], "amount sched. line", COUNT(CUSTO[Schedule_line_nr])
)

Can you help me? Thank you very much! 

[amitchandak]

@jessicarocha , You can use merge in Power Query or you can use Lookup, related, related table, Filter with joins. But in all cases tables from one table at a time and you can further do calculations

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo