matrix visual

[Alicia96]

I have the followig two csv files in power bi

Table 1:

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
9/29/2023	SO5789	tray	41	a1
9/27/2023	SO3652	Rack	65	r4
9/26/2023	SI5420	Nut	100	d2
9/22/2023	SO3476	Sheet	36	s5
9/22/2023	SO3476	tray	30	a1

Table 2:

Date	prods	order	qty	rqty
10/15/2023	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

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.

[Anonymous · Accepted solution]

Hi @Alicia96 ,

Try use merge query in power query editor:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum