cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JonAv
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

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

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.

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?

CahabaData
Memorable Member
Memorable Member

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

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

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!

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!

 

CahabaData
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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.