cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

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

Accepted Solutions
Highlighted

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

13 REPLIES 13
Highlighted
Memorable Member
Memorable Member

take a look at relationships;  you may find join lines that shouldn't exist; this can occur in my experience if one is adding tables/columns and there is an identical column name - sometimes it seems a join line is made automatically....

 

the error messages definitely indicate inappropriate join lines;  the only join line should be between fundamental key fields

www.CahabaData.com
Highlighted
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.

Highlighted

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?

Highlighted

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
Highlighted

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

Highlighted

I faced the exact same issue. And when I clicked "Manage Relationships" on the Home tab, I found that automatically two tables were joined, and it made no sense to join them both. Once I deleted the relationship, and published the changes, I was able to refresh the tables data!

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

Highlighted

Super, it really worked for me. I had the same issue and i was struggling to get resolved this issue.

 

I had no idea what was the wrong in my table and all.

 

thanks for this!

 

Highlighted
Frequent Visitor

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors