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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JonAv
Microsoft Employee
Microsoft Employee

Get "not allowed for columns on the one side of a many-to-one relationship" errors

I have a pbix file that successfully loaded data and everything worked.  The next day I tried refreshing it, and got this peculiar error:

 

Column ‘MAMUserPercent’ in Table ‘CapacityReport_ASUDeviceRatios’ contains a duplicate value ‘0.5’ 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.

 

I could not find any reason for the error, either in the table itself or in any references to the table.  Since I was out of ideas, I duplicated the table, changed all references to the new copy, and deleted the old one, and the refresh then failed with a similar error but on a different table:

 

Column ‘Total Device Check-ins Per Day’ in Table ‘CapacityReport_MonthlyCapacity’ contains blank values 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.

 

I repeated the process and the next refresh succeeded.  I'm worried that I will continue to hit this randomly when i refresh.  Any ideas on what's going on?

1 ACCEPTED SOLUTION
JonAv
Microsoft Employee
Microsoft Employee

Thanks.  I took a look at Manage Relationships and discovered that for some reason it had created a couple of very strange relationships automatically for some reason.  Those relationships were based on a seemingly random property between two tables that were related but based on completely different properties.  No idea why those were created, but I disabled them and hopefully that will keep the problem from recurring.

View solution in original post

24 REPLIES 24
nzhome
New Member

Yess, I too, have this exact same issue.   for this reason I will no longer use Power Pivot ever again.  Microsoft, this PowerPivot product simply does not work.  I am on the latest version in 2024. and I have 10 years programming and database experience.

chan005
New Member

Hi Guys,

It seems like you're encountering "not allowed for columns on the one side of a many-to-one relationship" errors in your database setup. This error typically occurs when you're trying to establish a many-to-one relationship between tables, but the column you're using on the "one" side doesn't match the primary key of the referenced table. Double-check your table schemas and ensure that the column you're referencing on the "one" side corresponds to the primary key of the related table. This alignment is crucial for establishing the relationship correctly and avoiding such errors. Magic 8 ball online

Evans_Thulare
Regular Visitor

Hi, Check if your table contains null values. I solved it by filtering null out of the table.

ByronPAWB
New Member

I had a similar issue to this. I had added work orders and work order services to my data sets. At first, it worked when loading data but later on I discovered if I add more than one line item, the data load failed with the following message.

 

Column 'msdyn_workorder' in Table 'msdyn_workorderservice' contains a duplicate value 'CE008731-F2C4-EC11-A7B5-0022481105BD' 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.

 

After some serious google searching, I found that PowerBI created a one-to-one relationship between the work order and work order products when it should have been one-to-many. Once I changed the relationship in the model, the data loaded without issue.

 

This works for me. There is a bad relationship that PowerBi have made during processing my data sets.

Anonymous
Not applicable

I followed these steps

 

Step 1: Go to the Model section from the left side of the Power BI Desktop

 

Step 2: Delete all the relationships (or connections) amongst the tables that have been created by Power BI itself while you were working with the Power Query Editor

 

Step 3: Click 'Refresh visual and data' option in Home (besides the Transform Data button) It worked and loaded the new data and also applied the automations done in the query editor.

Yes going to model and deleting the relationship of newly created table with the existing WORKED for me. Thank you...

It works! thanks!

sabirguiri
Frequent Visitor

The best practice that I would suggest is:

 

1. Go to Model on the left bar and then click 'Manage relationships' under the 'Home' menu.

2. Select which relationship is causing the issue and then delete it.

3. Open Power Query and then go to the 'Add Column' menu and click 'Index Column From 1'

4. Refresh all queries and then click 'Load & Apply'.

 

This worked well for me without having to delete records.

 

Sabir

This worked for me (THANK YOU!!!) but I would like to understand the rationale behind it. Any insight?

Anonymous
Not applicable

Hello,

 


I'm having the same issue when refreshing, but in my case I don't have anything in Manage Relationships , I only have one and it has nothing to do with the columns that may have "blank values". I deleted it just in case, and tried again but still getting the same error.

I need to switch from Direct Query to Import but due to this error the step is not completed.

Any clue what can be besides the relationships?

 


Thanks!

 


Julieta.

I know this is an old thread but I had this exact same issue and couldn't find a good answer anywhere. I removed all relationships and made sure that PBI wasn't set to do any automatic joins but was still getting the error that it couldn't have blank values on the one side of a many-to-one relationship, even though the random columns it was picking weren't utilized in any joins either in the model or in Power Query.

 

These are the steps I did that finally made it work again for me:

  1. Go into Power Query editor
  2. Remove all columns for that table except the primary key
  3. Apply changes
  4. Go to the Model view and set the table to Import (it worked this time since it didn't have any columns to complain about)
  5. Go back to Power Query editor
  6. Remove the step where you removed all the other columns
  7. Apply changes

I think it may be a bug where PBI had tried to auto-detect relationships in the back end of the software and retained some strange joins, but I can't verify it.

 

Hopefully this helps someone not spend an hour banging their hands on their desk like I did.

Hello, 

 

I have two datasets that I am attempting to merge using Merge Queries. The primary table has key identifier "location" (used for left join), and secondary table has other information about the location as of different dates. I want the result to have duplicates from the primary table and build out a new table with the various bits of information from the secondary table. This error is appearing and I am unsure of how to resolve. 

 

Thanks in advance for your help. 

I had this same issue.  I got so angry with PowerBI that I had to put it away for 2 days.  My PBI report that used to work just fine suddenly had all kinds of this error, and a few OLEDB (??) errors in loading tables.  I've got several similar tables, with same column names, but they're all for completely different accounts, so there should be no 'relationships' between them.  The only thing that changed (aside from some new rows of data) was that I rebuilt my PC.  So, new install of PowerBI.  It seems like PowerBI will automatically create relationships if there are columns in two different tables that have the same name.  Which is dumb.  So, I went in and deleted every single relationship.  (It's under Modeling->Manage relationships, for those who can't find it in the Home menu, like some documentation floating around out there says.)  After that, everything is back to normal.  

 

So, if this error suddenly crops up on you for no reason, it's probably a newer build of PowerBI that is doing some automatic relationship building on columns with the same name.  Delete all the relationships and ignore the warnings.

I'm getting this same error and have no relationships set up. I'm thinking this is a bug. The field it references does not contain blank values. How does one clear this out?

Anonymous
Not applicable

I found a workaround for this by switching the column order of my ID column (which was giving me the error) and the createdDate column from the same table. The CreatedDate is actually a Date/Time value and so perhaps there weren't any distinct values in there? I'm not certian if that's what did the trick. 

ankitpatira
Community Champion
Community Champion

@JonAv On top of what @CahabaData said that error indicates you have duplicate values in the column that is used as a primary key for the relationship. You should also check that there are no duplicates.

Thanks for the responses.  I realize that is what the errors indicate, but that is not what seems to be happening.   The columns it's complaining about seem to be random numeric properties within the dataset.  They are not referenced by any joins.  Further, if there was a problem like that why would duplicating the table and changing the reference to the new copy fix the issue, even temporarily?

When you say they are not referenced by any joins.... do you mean that when looking at the relationships screen that there are no join lines at all?

 

What we do know is that refresh is going to trigger all the steps of the Query Editor that apply themselves to model the data.  While your manual duplication of a table does not do that.  So an answer might be seen in inspecting all the steps of the Query Editor to see if that provides any insight.

 

The last thing is of course the data itself, as a refresh implies a new set of data.  One should compare a refresh of the original data, with a refresh of the new set of data to see if somehow the data content itself is triggering these errors....difficult to understand how that could be - but.......

 

 

www.CahabaData.com
JonAv
Microsoft Employee
Microsoft Employee

Thanks.  I took a look at Manage Relationships and discovered that for some reason it had created a couple of very strange relationships automatically for some reason.  Those relationships were based on a seemingly random property between two tables that were related but based on completely different properties.  No idea why those were created, but I disabled them and hopefully that will keep the problem from recurring.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.