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
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.
Super UserHi 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
Thank you MFelix! That resolved the error. I appreciate your help.