Getting duplicate values in matrix visual

[Alicia96]

I have the followig two csv files in power bi

Table 1:

Date Product ref Quantity rqty
10/15/20223 Rack SO5658 54 30
10/3/2023 Nut SO3214 36 64
10/3/2023 Screw SO3214 20 60
10/3/2023 Sheet SO3214 50 250
9/29/2023 Sheet INV 50 300
9/29/2023 tray SO5789 41 44
9/27/2023 Rack SO3652 65 84
9/26/2023 Nut INV 100 100
9/22/2023 Sheet SO3476 36 250
9/22/2023 tray SO3476 30 85

Table 2:

Date Ref Product Quantity Lot
10/13/2023 SI2502 Tray 26 a1
10/3/2023 SO3214 Nut 36 d2
10/3/2023 SO3214 Screw 20 s6
10/3/2023 SO3214 Sheet 50 s5
10/3/2023 SI2489 Tray 100 a1
9/29/2023 SI6535 Sheet 50 s5

I want a matrix visual in Power BI as follows:

Date Ref Product Quantity Lot rqty
10/3/2023 SO3214 Nut 36 d2 64
10/3/2023 SO3214 Screw 20 s6 60
10/3/2023 SO3214 Sheet 50 s5 250
9/29/2023 SO5789 tray 41 a1 44
9/27/2023 SO3652 Rack 65 r4 84
9/22/2023 SO3476 Sheet 36 s5 250
9/22/2023 SO3476 tray 30 a1 85

but i am getting the following output

Date prods order qty rqty
10/3/2023 Nut SO3214 36 64
10/3/2023 Nut SO3214 36 60
10/3/2023 Nut SO3214 36 250
10/3/2023 Screw SO3214 20 64
10/3/2023 Screw SO3214 20 60
10/3/2023 Screw SO3214 20 250
10/3/2023 Sheet SO3214 50 64
10/3/2023 Sheet SO3214 50 60
10/3/2023 Sheet SO3214 50 250
9/29/2023 tray SO5789 41 44
9/27/2023 Rack SO3652 65 84
9/22/2023 Sheet SO3476 36 250
9/22/2023 Sheet SO3476 36 85
9/22/2023 tray SO3476 30 85
9/22/2023 tray SO3476 30 250

The rqty column is showing duplicate values.  

Any help would be appreciated.
Thank you.

[lbendlin]

Your data is not mapping well between these two tables, even assuming the composite key suggested by @mickey64 

Note that Power Query is case sensitive, so Tray and tray are not the same.

Please verify your sample data, and explain the rules again.

[mickey64]

For your reference.

Step 0: I use these DATA.

Table 1:

Date Product ref Quantity rqty
10/15/2023 Rack SO5658 54 30
10/03/2023 Nut SO3214 36 64
10/03/2023 Screw SO3214 20 60
10/03/2023 Sheet SO3214 50 250
09/29/2023 Sheet INV 50 300
09/29/2023 tray SO5789 41 44
09/27/2023 Rack SO3652 65 84
09/26/2023 Nut INV 100 100
09/22/2023 Sheet SO3476 36 250
09/22/2023 tray SO3476 30 85

Table 2:

Date Ref Product Quantity Lot
10/13/2023 SI2502 Tray 26 a1
10/03/2023 SO3214 Nut 36 d2
10/03/2023 SO3214 Screw 20 s6
10/03/2023 SO3214 Sheet 50 s5
10/03/2023 SI2489 Tray 100 a1
09/29/2023 SI6535 Sheet 50 s5
09/29/2023 SO5789 tray 41 a1
09/22/2023 SO3476 Sheet 36 s5
09/22/2023 SO3476 tray 30 a1
09/27/2023 SO3652 Rack 65 r4

Step 1: I add a column to both tables.

    Key_ID = [Date]&"_"&[ref]&"_"&[Product] 

    

    

Step 2: I add a relationship.

    

Step 3: I make a matrix.

    

[lbendlin]

Please show your data model view.