Forum Discussion
Solved
Table merging issue?
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
pls try this
4 Replies
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.
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
