How to model this in Power Query

[SHDJason]

Hi, I'm new to data modeling with Power Query so please forgive if this is rudimentary stuff. But I need some guidance.  

I have 2 tables.

Table 1 has products and sales.

Table 2 has products and returns.

Table 1 has products  = A, B, C

Table 2 has products = B, C, D

For products B and C, I want to show sales and returns.

For the other unique products, I want to show which do not match (i.e. A only has sales and D only has returns).

I have been doing this by appending the spreadsheets, then pivot and use the Nulls as the exceptions.  But this is not sustaible anymore. 

What is the best way to model this?  A many-to-many join? Or a product lookup table of some sort? 

What happens with the products also have categories?

Thanks for your help.

[MattAllington · Accepted solution]

How about you append the 2 tables, but instead of pivoting the data, simple create a new column called "type". This new column will have the value "sales" or "returns". To do this, load both tables into power query one at a time. Add the new column for each table and hard code the correct value. Then append both tables. 

Assuming you then load this data to power pivot, you can then use a pivot table or measures to complete the process. This shape is preferable for power pivot anyway.