Apply a value filed of one item to a related item using references in a separate table

[KarenQ]

I have cost of Component A $4 and cost of Finished Goods B $5. A and B share the same Item Code "220".

Since A is part of B in the case, I intend to build a seperate column for Finished Goods B naming " Component A" along side the total cost of Finished Goods B $5. There are thousands of other items with the similar setup. Aiming to finally come up with a Finished Goods table with total cost, components etc. to disiplay the cost breakdown. 

Here A is uniquie to B, then maybe more than one FG itmes like B exists, say, C and D. 

A,B,C,D share the uniqure item type code "220"

Wonder how to make it happen. 

Below an example.

	Table 1				Table 2
	Item	Cost			Item	Code	Type
A	22050	$        4.00			22050	220	Component
B	22050DD001	$        5.00			22050DD001	220	FG
C	22050DD002	$        6.00			22050DD002	220	FG
D	22050DD003	$        7.00			22050DD003	220	FG
	Aim to have the below :
		Cost Total	Component Cost
	22050DD001	$        5.00	$                      4.00
	22050DD002	$        6.00	$                      4.00
	22050DD003	$        7.00	$                      4.00

[rubayatyasmin · Accepted solution]

Hey, @KarenQ 

Thanks for reaching out. I tried to solve it using PQ. 

Here is the output. 

FindOneItem.pbix 

[KarenQ]

Thanks.

Could you send me the rawdata file?

[rubayatyasmin]

copied de,o data that you provided for table 1 and table 2.