Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Relationship error when splitting column into rows

Hello, I am new to Power BI and learning as I go (mostly from this forum!). I have a two tables that are related. The first is opportunities (Salesforce)

 

Id             Name           Amount

8001...     Opp1            $50

8002...    Opp2             $75

8003...    Opp3             $40

 

The second table is called Proposals. It has a many to one relationship with opportunities.

 

Proposal Id  Opportunity  Amount    Countries

p019            8001...            $50           Ghana; Nigeria

p020            8001...            $75           Ghana; Nigeria; Zimbabwe

 

I am working on building a report that includes a slicer for country and a map visual. I think the right approach here is to split the countries column into multiple rows. However, when I do that, I get an error message that says:

 

"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."

 

This makes sense to me, because now the table has multiple rows of proposals with the same opportunity Id. I'm wondering if I need a different approach or if it's possible to create a separate table, just for countries, that has a relationship to proposals, rather than to opportunities, but I'm not sure how to do that.

 

I appreciate any help or ideas on where to find documentation or tutorials on this - I've found plenty of things about splitting columns but nothing about how to handle this relationship error as a result of creating multiple rows like this. 

 

Thanks!

Alex

1 ACCEPTED SOLUTION

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @Anonymous,

This 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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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!

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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 IdNameOpportunity IdCountriesIsLatest?
a001Proposal - 0048000081USA; Ghana; LiberiaTRUE
a002Proposal - 0048100082Lithuania; FranceTRUE
a003Proposal - 0048200083Australia; JapanTRUE
a004Proposal - 0048300084France; Spain; ItalyTRUE
a005Proposal - 0048300084France; SpainFALSE

 

Opportunity IdAccountIdNameAmount
0008100013Opportunity 1$100,000
0008200013Opportunity 2$4,000
0008300014Opportunity 3$6,000
0008400015Opportunity 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 IdNameOpportunityCountriesIsLatest?
a001Proposal - 0048000081USATRUE
a001Proposal - 0048000081GhanaTRUE
a001Proposal - 0048000081LiberiaTRUE
a002Proposal - 0048100082LithuaniaTRUE
a002Proposal - 0048100082FranceTRUE
a003Proposal - 0048200083AustraliaTRUE
a003Proposal - 0048200083JapanTRUE
a004Proposal - 0048300084FranceTRUE
a004Proposal - 0048300084SpainTRUE
a004Proposal - 0048300084ItalyTRUE
a005Proposal - 0048300084FranceFALSE
a005Proposal - 0048300084SpainFALSE

 

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. 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thank you @MFelix! That resolved the error. I appreciate your help.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors