Table merging issue?

[gianmarco]

Dear All,

I have two tables:

FACT TABLE

Item	Colour	Size	Value
1	5	9	€ 4.385
1	6	10	€ 3.160
2	5	9	€ 3.622
2	6	10	€ 9.085
2	7	11	€ 5.169
3	8	11	€ 9.695
4	6	9	€ 6.620
4	8	10	€ 4.355

TEXT TABLE

ID	Value
1	Ring
2	Bracelet
3	Crown
4	Necklace
5	Yellow
6	Green
7	Blue
8	Black
9	Small
10	Medium
11	Large

My desired result is the following:

NEW FACT TABLE

Item	Colour	Size	Value
Ring	Yellow	Small	€ 4.385
Ring	Green	Medium	€ 3.160
Bracelet	Yellow	Small	€ 3.622
Bracelet	Green	Medium	€ 9.085
Bracelet	Blue	Large	€ 5.169
Crown	Black	Large	€ 9.695
Necklace	Green	Small	€ 6.620
Necklace	Black	Medium	€ 4.355

I don't think I can manage this through relationships because it is admitted only one relationship between tables. Is merging of queries one for each column the only solution? 

Thank you

gianmarco

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[gianmarco]

Dear @Ahmedx , I hope i didn't have to use merge queries each for every column. This is a sample dataset but actually my fact table consists of 30 columns. I thought there could be a more effective solution. 

[Ahmedx · Accepted solution]

pls try this

[ray_aramburo]

You can achieve it both ways. With relationships (that catalog table will have a 1 to many relationship to the fact table, using the ID column) or using Merge queries within Power Query.