Lookupvalue returns all values

[Piinherio]

Hello all, 

I have two tables shown below, I want to bring the PO number and Qty from table 2 to table 1 without merging queries. I tried to do lookupvalue but it only brings one PO number. is there any solution for this. 

Thanks in advance.

Table 1

Material Number	Material Desc	Qty
12	AAA	10

Table 2

Material Number	PO Number	PO Qty
12	159753	2
12	794631	4
12	896513	4

Results 

Material Number	Material Desc	Qty	PO Number	PO Qty
12	AAA	10	159753	2
			794631	4
			896513	4

[lbendlin]

Let the data model do the work.  Join the tables by the Material Number.

[Piinherio]

Thank you for your reply. I already made a relationship based on material number, but when there is no P.O for a spesfic material (Material Number 13), the material number disappear from the table. as shown below. 

[lbendlin · Accepted solution]

Enable "show items with no data"

[Piinherio]

is there anyway to replace the blanks cells with a text? 

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins