Forum Discussion
Pivot - Several databases - Many to Many
Hi,
In order to make your datamodel work properly, you ideally create a Customer Dimension.
This is a list with all unique Customers and optional more information on them.When you created this, you can simply connect both as you call it "databases" to the same table with a 1 to many connection(always try to stay from many to many)
How to create this?
Idealy, just go to PowerQuery --> Right click on a table that contains all Customers --> Reference --> Remove all columns except customer(key) --> Remove duplicate values of Customers
Now you have a list of all unique customers, this you can connect to all other tables containing multiple customer rows.
Hi Anonymous ,
I suggest to concatenate Date, Customer and Product columns in both databases. Then, link both databases via the concatenated columns.
That should do it.
Let me know if anything is unclear.
Cheers,
Robin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Anonymous ,
Thank you for your answer. I didn want to concatenate theses columns because I need to use them separately. Finaly I renamed the columns with exactly the same name then it works but I have the same problem with another column.
Indeed, if I want to see the complaints and sales by week, I have the same problem :
DESIRED OUTPUT
| Week | Complaints | Sales |
| 12 | 18 | 789456 |
| 13 | 5 | 546585 |
| 14 | 25 | 45821 |
| 15 | 23 | 85468 |
| 16 | 85 | 87458 |
| 17 | 220 | 88754 |
What I have :
| Week | Complaints | Sales |
| 12 | 376 | 789456 |
| 13 | 376 | 546585 |
| 14 | 376 | 45821 |
| 15 | 376 | 85468 |
| 16 | 376 | 87458 |
| 17 | 376 | 88754 |
When I do "see records", I see that only one of the database is properly filtered as you can see below :
Many thanks for your help :)
Hi,
In order to make your datamodel work properly, you ideally create a Customer Dimension.
This is a list with all unique Customers and optional more information on them.When you created this, you can simply connect both as you call it "databases" to the same table with a 1 to many connection(always try to stay from many to many)
How to create this?
Idealy, just go to PowerQuery --> Right click on a table that contains all Customers --> Reference --> Remove all columns except customer(key) --> Remove duplicate values of Customers
Now you have a list of all unique customers, this you can connect to all other tables containing multiple customer rows.Hello RobbeVL,
You was right, stay far from many to many. Now it works perfctly.
Many thanks all of you,
I don't think Anonymous was suggesting you concatenate the columns, but was suggesting you concatenate the tables. It sounds like you accomplished this by changing the column names to match.
If I had to guess, I would think that your Complaints and Sales tables are still not related to each other. A normalized relational data model would be a huge benefit for you. Your time is probably best spent here. Could you share a screenshot of your table relationships, or even better a sample .pbix file so we can see how your data is set up? I understand if you have a need to anonymize your data, but a dozen or two rows of data of random values set up in the same format would be exceptional.
