Merge Table with Power Query. Primary Key with Duplicate values

Hello

Attach an excel file Name ?Factory?
3 Different departments will input data separately.
( Input Data = Mark in Yellow)
Sheet: Merchandising = Table Name: Merchant
Sheet: Com_Imp = Table Name: Com_BBLC
Sheet: Com_Imp = Table Name: Com_Imp
Sheet: Com_Exp = Table Name: Com_Exp

Sheet Name ?Result?,
I try to collect all data from all tables into a Master Table.

Try to get different kinds of results from the Master Table using Pivot table
But my result comes WRONG only the reason Primary key ?Order Number? is duplicate
Result I try to find in Main sheet Cell no :AM2
Result comes :
JMS Order No: 001
Qty is OK: 50000+60000+7000=180000
The amount is OK: 589,000
BBLC Value is wrong: 418,190 should be 429,970
IMP Value is wrong: 388,560 should be 429,790
Exp Value is wrong: 259,000 should be 587,350

The only reason of this wrong calculation is ?Order Number? is duplicate
I marked in RED all the Tables.

I tried query with Merge.

is that what you want?

Order No	JMS-001-22
Qty_e	Sum of Merchant.Amount	Sum of Com_BBLC.BBLC-Amount	Sum of Com_Imp.Amount	Sum of Exp Amt
25000	23560000	12899100	10314960	9600000
29500	11780000	6449550	5157480	5841000
30000	11780000	6449550	5157480	5940000
70000	11780000	6449550	5157480	13860000
Grand Total	58900000	32247750	25787400	35241000

No sandy666
this is not what i want

Originally Posted by fkarim74

No sandy666
this is not what i want

sorry about that

If you check my excel sheet
you found

order number JMS-001-22

#
Order qty is : 180,000 Pcs
Amount: $ 589,000

#
BBLC : $ 429,970

#
Import : $ 429,790

#
Export : $ 587,350

There are
3 sheets
(Merchandising, Com_Imp, Com_Exp)

4 Tables
(Merchant, Com_BBLC, Com_Imp, Com_Exp)

from these tables, the Primary key or lookup value is "Order Number"

Order wise I need all information as per requirements

Hope understand.

do you know BBLC and Import doesn't contains QTY ?

maybe this one

Order No	JMS-001-22
QTY	Sum of Merchant	Sum of BBLC	Sum of Import	Sum of Export
25000				160000
29500				97350
30000				99000
50000	160000
60000	198000
70000	231000			231000
(blank)		429970	429790
Grand Total	589000	429970	429790	587350

Yes

//do you know BBLC and Import doesn't contains QTY ?//

BBLC and Import is related with the amount

so here is

Thanks Sandy.

I am working on it.

My pleasure