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

Question

i have created Power bi project, It is working fine in the beginning but when i refresh my datasource , i get this error " not allowed for columns on the one side of a many-to-one relationship" . Any can help me ??

Answer 1

I had the same issue, after spending hours of searching for a fix and not finding anything, I started scratching around and found the problem took 1second to fix. My issue specifically, was a an additional relationship created within the model. The connection showed up as a "dotted" line one of my tables. I deleted the relationship, refreshed. Done.

Answer 2

I got this error on a completely new table made in Power Query, weird since I had no chance to create a relation yet.

Easy fix: Apply a filter that removes all blanks on that column - apply the filter, and then delete this new filter again

Another problem you might have is that "Autodetect new relationships" is turned on: This is a setting that disables auto-detecting relations under "File/Options & settings/Options/Current File/Data Load/Relationship/Autodetect new relationships after data is loaded"

Answer 3

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.

Answer 4

This happens when your table or connected tables in excel, contain blank row, for resolving this issue you will need to click anywhere in the excel table, then click on the table tools, then resize table and Select the entire range of cells to include all rows, make sure no blank rows are included then save and get back to your PowerBI then again refresh it, all will work.

Answer 5

None of these answers helped me. For me I received this error when trying to refresh my dataset which had previously worked for some time. After investigating I found the schema of the source database had changed. Two fields that previously didn't allow nulls now allowed nulls and had null values for some rows. My Power BI model still expected these fields not to contain nulls but was throwing this same and very misleading error:

Data source error: Column 'x' in Table 'y' 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. Table: y.

Initially on seeing this error and opening up my report in PowerBI Desktop and going to Modeling > Manage relationships. I looked for a relationship on table y for column x, but no such relationship existed!? Was I confused? You bet.

After investigating further I discovered the database schema change and resolved by updating my Power BI model by going to the data model editor, expanding table y in the Fields panel on the right hand side, selected field x, expanded "Advanced" in the Properties panel and changed "Is nullable" from No to Yes. I then applied the changes, saved the report and refreshed the dataset.

Answer 6

I resolved this issue by going into the relationship, right clicking to view properties, and making it a Many to One Relationship.

Answer 7

In my case issue was related to the fact that Power BI was threating "SQL View" as a "Table" and as a result it was creating relations for it.

Although I've checked the "Manage Relationships":

and removed one relation which was not reasonable the issue was still persisting.

Then issue solved when I had looked in the "Relations"

tab and found unreasonable relations related to my View and removed them.

Answer 8

Power BI sometimes automatically creates relationships between the queries that are being used to drive the data in the reports. When I have encountered this error or errors like it in the past I:

• Go into Manage Relationships
• Verify that there is a relationship listed
• Evaluate the From and To relationships that are listed as active
• Delete any invalid From and To relationships between separate data sets

My most common issue in the past has been that I will have two very different queries pulling data from separate sources with similar column names and Power BI will generate a relationship between them that is invalid. After removing the relationship it has always resolved my issue.