Forum Discussion
Relationship error when splitting column into rows
Hi Anonymous ,
Looking at your mockup data and putting it on a PBIX file I got no error I created a one (Opportunities) to many (Proposals) relationship between both tables.
But reading your post again I detected that you wrote:
"Proposal - Column 'Id' in Table 'Proposal' contains a duplicate value 'a001U0000064hRXQAY' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."
Believe that your issue is in the way you setup your relationship, you need to add a relation that links the Opportunity ID (Opportunity table) with Opportunity ID (Proposals Table) be aware that the one side is on the opportunity side and not on the proposal as you refer.
Check the PBIX file attach with your mockupdata.
Regards,
MFelix
Super UserThis error is giving because you have the relationship active so the change you made are not passing to the final model.
You should create a new table with unique I'd of opportunities and them relate this new table with the other to tables so this dimension table will act as the connector on relating the other two tables.
In summary you would have 3 tables
Opportunities
Proposals
IDoppprtunities (only unique values)
And 2 relationship one to many between the IDopportunities and the other two tables.
Regards,
MFelix
Hi MFelix Thank you for your reply. That makes sense, and I was able to create a new table with the distinct values from the Opportunity and set up those relationships. But, now I'm getting an error that says that it's "not allowed for columns on one side of many to one relationship". There are duplicate values in the proposal Id column, which is the primary key for that table.
I checked and there are no other relationships between the proposal table and the Idopportunities table. I guess the issue is that it's because the proposal Id is the primary key for that table, and now there are duplicates? Perhaps I should duplicate the entire proposal table so I can make that proposal Id column a foreign key?
The basic issue I have is that I have some fields in my data that are semi-colon separated values, and so I am trying to transform my data to work with them, ideally as separate rows.
I appreciate your help!
- MFelix
Hi Anonymous ,
If you have a table with unique ID the error you should have no errors when connecting this table with the other two.
Can you share a sample of the file? If information is sensitive send it trhough private message or a mockup data will do.
Regards,
MFelix
Hi MFelix ,
I believe the error is due to splitting the proposal table into rows, so there are now duplicates of the Proposal Id field in that table. Here is some mockup data.
I have a table of proposals which is related to opportunity via the opportunity Id field:
Proposal Id Name Opportunity Id Countries IsLatest? a001 Proposal - 00480 00081 USA; Ghana; Liberia TRUE a002 Proposal - 00481 00082 Lithuania; France TRUE a003 Proposal - 00482 00083 Australia; Japan TRUE a004 Proposal - 00483 00084 France; Spain; Italy TRUE a005 Proposal - 00483 00084 France; Spain FALSE Opportunity Id AccountId Name Amount 00081 00013 Opportunity 1 $100,000 00082 00013 Opportunity 2 $4,000 00083 00014 Opportunity 3 $6,000 00084 00015 Opportunity 4 $25,000 I would like to split the countries field so that I can report on the individual values. When I split that field into new rows, the data looks like this:
Proposal Id Name Opportunity Countries IsLatest? a001 Proposal - 00480 00081 USA TRUE a001 Proposal - 00480 00081 Ghana TRUE a001 Proposal - 00480 00081 Liberia TRUE a002 Proposal - 00481 00082 Lithuania TRUE a002 Proposal - 00481 00082 France TRUE a003 Proposal - 00482 00083 Australia TRUE a003 Proposal - 00482 00083 Japan TRUE a004 Proposal - 00483 00084 France TRUE a004 Proposal - 00483 00084 Spain TRUE a004 Proposal - 00483 00084 Italy TRUE a005 Proposal - 00483 00084 France FALSE a005 Proposal - 00483 00084 Spain FALSE So I am wondering if the issue is that the Proposal Id now has duplicate values, and since it is the primary key for that table it is causing the error.