Error contain blanks value in column on one side of table with one-to-many relationship

[huonghill]

Hi,

I'm trying to update my data source with a new Excel file on SharePoint. After hiting refresh, I got this error:

Column "ABC" in Table "XYZ" 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 check the new spreadsheet and there is new blank value in "ABC" column. 

Table "XYZ" has one-to-many relationship with the other tables. I'm trying to reverse engineer this pbix so I don't want to change any table relationship. 

Please help me if you have any idea how to fix this. Thanks in advance!

[Syndicate_Admin]

Good day, how can I put within this measure that I fill the blanks by a zero

CALCULATE(COUNTROWS('NITS WITH ACTIVITY 2022 STATESvsCOMPLETE ZONES'),FILTER('NITS WITH ACTIVITY 2022 STATESvsCOMPLETE ZONES','NITS WITH ACTIVITY 2022 STATESvsCOMPLETE ZONE'[Type]<=0))

[Syndicate_Admin]

in two tables that have a ratio of 1->M (one to many). the table containing unique values cannot have empty or null records or duplicate records.

At the level of the cleaning process in Power Query they must validate and debug so that these two conditions are met:

• Remove duplicates from the key column
• Remove voids
• Remove nulls (not the same as empty)

Once the changes are applied, the relationship should work correctly.

[Syndicate_Admin]

the same thing happens to me, if please you can give us a hand to those who have this difficulty, thank you

[parry2k]

@huonghill table which is on one side of the relationship cannot have a blank/null value, check your data, and make sure columns don't have blank values.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[huonghill]

@parry2k Table "XYZ" is on the one side of the relationship. I checked and there is no blank values 🙂

[parry2k]

@huonghill how you are checking there is no blank value?

[huonghill]

@parry2k 

It's a small table in Excel. I checked the new Excel Spreadsheet and filter the column. I saw no blank values

[parry2k · Accepted solution]

@huonghill in Power BI?? Put table visual and column in question in the table visual and sort it in the visual and check you get blank value on the top. It doesn't matter what you see in Excel but what we get in Power BI is what we are interested in, there might be blank rows that you don't see in Excel.

[huonghill]

you are right. In pbi, there are null values. Thanks for your help, @parry2k !

[Albiena]

Finding same error . How do I clean the null values 

[Syndicate_Admin]

@parry2k @huonghill I do not understand this part "Put the visual table and column in question in the visual of the table and Ifiled your image in the visual and check that you get a blank value at the top" could explain more in detail please?