Create new table based on columns from related tables keeping all the values in the columns

[Chris2016]

Hello,

I need help with the following issue:

I have two tables where the following columns exist:

TableA

Category	Product
A	Cars
B	Planes
C	Trains
D	Bicycles

TableB

Products	Color
Cars	Blue
Cars	Green
Cars
Planes	Blue
Planes	White
Planes
Trains	Blue
Trains	Black
Trains

I need to create a new table that contains three columns from the two tables, where all the values of all the columns are kept, i.e.:

Category	Products	Color
A	Cars	Blue
A	Cars	Green
A	Cars
B	Planes	Blue
B	Planes	White
B	Planes
C	Trains	Blue
C	Trains	Black
C	Trains
D	Bicycles

Any help in achieving this is much appreciated.

Thanks!

[Fowmy · Accepted solution]

@Chris2016 

First create a relationship between table A and table b using the field products. Then create the following table to have the desired results as a table.

New Table = 
SELECTCOLUMNS(
	NATURALLEFTOUTERJOIN( TableA , TableB ),
	TableA[Category],
	TableA[Product],
	TableB[Color]
)

[Chris2016]

Thanks a lot for your help, Fowmy.

I do have a relationship like you mentioned:

 While in my test pbix I have an error in the formula, the table is displayed as expected.

However, in my actual report I get this error:

The relationship I have between TableA and TableB is between the IDs of columns Product, but it is a many to many relationship. Is this what's effecting the NATURALLEFTOUTERJOIN function?

Thanks a lot!

[Fowmy]

@Chris2016 

Yes, Many to Many relationships will not work in this case. I am not sure what your are trying to achieve by mergeing these tables. However, please try to keep one to many relationship by having a relevent dimension table.