Forum Discussion

Alicia96's avatar
Alicia96
New Member
2 years ago

Getting duplicate values in matrix visual

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.

Need Help
Show and Tell

3 Replies

  • mickey64's avatar
    mickey64
    Super User
    2 years ago

    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's avatar
    lbendlin
    Super User
    2 years ago

    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.