Power Query table merge

[TDC21]

I have two tables, one is for dimensional inventory on hand, the other for dimensional inventory purchased. I am trying to find a way to create 1 table that will combine data from both but no luck so far. For example,

ON ORDER					ON HAND					Desired Result
PART_ID	LENGTH	WIDTH	QTY		PART_ID	LENGTH	WIDTH			PART_ID	LENGTH	WIDTH	ON HAND	ON ORDER
15000SA51670	518	86	2		15000SA51670	350	278	1		15000SA51670	518	86	1	2
15000SA51670	518	99	2		15000SA51670	89	89	1		15000SA51670	518	99		2
15000SA51670	498	106	2		15000SA51670	518	86	1		15000SA51670	498	106		2
15000SA51670	498	112	1							15000SA51670	498	112		1
										15000SA51670	350	278	1
										15000SA51670	89	89	1

<colgroup><col><col><col span="2"><col><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Not all part ID's from one table are in the other, the length and width values are also variable between each table. My first thought was to merge both tables on Part ID, Length, and Width, as a Full Outer Join (all rows from both) but I end up with fewer rows than are in either table so I am pretty sure this must be incorrect.

Any thoughts are greatly appreciated, thanks

 

[Matt Allington]

Full outer join und right to me too. Another approach is to create a "type" column in each source table (i.e. On hand, on order all the way down), append the tables and then pivot the data.

 

[TDC21]

Thanks Matt, the Type column worked great, going to have to read up on appending queries but it let me join tables without duplicate or shared rows so a few extra if statements were enough to create consolidated rows that I was able to group off of.