How can I create this table or matrix ?

[Anonymous]

Hi community,
I have two tables just like the next examples;

Table 1

Item	Total Qty to Deliver	OnSite Available Pcs	Offsite Available Pcs	Remain	Total Available Pcs
A	320	192	288	-128	480
B	225	100	234	-125	334
C	245	37	214	-208	251
D	12	35	264	23	299
E	0	29	272	29	301

Table 2

Item	Pallet	Boxes	Pices
A	00000000000100079197	28	168
B	00000000000100179255	17	102
C	00000000000100230567	24	112
D	00000000000100021493	22	132
E	00000000000100126754	22	132
A	00000000000100291209	22	120
B	00000000000100299335	22	132
C	00000000000100183603	17	102
D	00000000000100030921	22	132
E	00000000000100079197	22	140

The Table 2 is the OffSite Available Stock, but it's splited by Pallet.
I need a table/matrix that gives me the pallets to bring for can supply the qty to deliver, trying to bring the most matchable pallet by qty between the remain that i need to complete & the qty in the pallet.

Something like this; 

Item	Pallets To Bring	Pallets To Bring ID	PCS
A	1	00000000000100079197	168
B	1	00000000000100299335	132
C	1	00000000000100183603	102
C	1	00000000000100230567	112
D	0	Null	Null
E	0	Null	Null

How can i make it possible on Power BI?
I've trying for days and I still have no idea about to create this table...

I hope you can help me, 
Thank you. 

[v-yingjl]

Hi @Anonymous ,

Seems like your expected output not very clear. For example, in your final table, why Item C should have two rows, how to define its calculation logic because I cannot see other relationship between two tables except items. Also, if you want Item D,E show null, maybe remain>0? But how to define other items...not certain

Perhaps need more details about this issue for further discussion.

Best Regards,

Yingjie Li

[Anonymous]

Hi @v-yingjl ,
Thanks to take time to try to help me, ill try to be more clear...
For example, in the case of item B, the request (qty to deliver) is for 225 pcs, On Site I only have 100, the output table or matrix gives me pallet 00000000000100299335 with 135 pcs cause I need 125 pcs more to complete the request.
In the case of C, (thats why I think it could be a matrix), the request (qty to deliver) is for 245 pcs, On site I only have 37 pcs, I need to bring 208 pcs more from the stock out of the site (table B).
In the table B, I have two pallets of item C;  00000000000100230567 with 112, and the 00000000000100183603 with 102, 214 pcs in total. Reason why the output table or matrix gives me the two rows for item C = the two pallets that i need to complete the request. 
In the case of D & E items, I dont care if i have or not rows of them in the output table, because i can supply the request with the Onsite pcs or dont have request. I think is more logical to dont have rows of them if i dont need to bring pcs. 
I hope I have been clearer, let me know if i need to explain something more or in a diferent way. Thank you.