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,


[TABLE="width: 887"]
<colgroup><col><col><col span="2"><col><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ON ORDER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ON HAND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Desired Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PART_ID[/TD]
[TD]LENGTH[/TD]
[TD]WIDTH[/TD]
[TD]QTY[/TD]
[TD][/TD]
[TD]PART_ID[/TD]
[TD]LENGTH[/TD]
[TD]WIDTH[/TD]
[TD][/TD]
[TD][/TD]
[TD]PART_ID[/TD]
[TD]LENGTH[/TD]
[TD]WIDTH[/TD]
[TD]ON HAND[/TD]
[TD]ON ORDER[/TD]
[/TR]
[TR]
[TD]15000SA51670[/TD]
[TD="align: right"]518[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]15000SA51670[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]278[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]15000SA51670[/TD]
[TD="align: right"]518[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]15000SA51670[/TD]
[TD="align: right"]518[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]15000SA51670[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]15000SA51670[/TD]
[TD="align: right"]518[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]15000SA51670[/TD]
[TD="align: right"]498[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]15000SA51670[/TD]
[TD="align: right"]518[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]15000SA51670[/TD]
[TD="align: right"]498[/TD]
[TD="align: right"]106[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]15000SA51670[/TD]
[TD="align: right"]498[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15000SA51670[/TD]
[TD="align: right"]498[/TD]
[TD="align: right"]112[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15000SA51670[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]278[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15000SA51670[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.