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

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

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!

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

10 REPLIES 10
Syndicate_Admin
Administrator
Administrator

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

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

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

parry2k
Super User
Super User

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

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

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.