"Contains blank values and this is not allowed" error - only in service not desktop

[jakeryan56]

I have suddenly started getting this relationship error when refreshing in the PBI service but I can refresh without any issues in desktop.

A couple of points:

1. This field in question has no relationships with any other tables

2. This field has no blank values as the error suggests

[JoeyBream]

I've had this issue for the past hour, just found a solution.

In my query I added a step to remove blank values (specifically, I de-selected <null>).

This surprised me as visibly I didn't have any blanks, but somehow the query had found some.

This is working better now.

[jakeryan56 · Accepted solution]

Ok after hours of playing around I finally found the solution but still don't understand why this was happening since there was no relationship with this field.
All I had to do is go to the model view, select the field in question -> Properties -> Change "Is nullable" to Yes.

[trisha29]

Thanks for posting! Worked for me in resolving similar issue

[AllisonKennedy]

@jakeryan56  Thanks for the solution, I'll add this one to my list. What was your data source that this was set to 'no' by default? Or do you think it's possible you might have changed it? Generally it defaults to 'yes' for most columns. I'm wondering if perhaps there used to be a relationship using this column? 

[Icey]

Hi @jakeryan56 ,

Glad to hear that you have solved the issue. Please accept your reply as a solution so that people who may have the same question can get the solution directly.

Then, for the "Is nullable" property, please check if this post could help you understand it better: The Is Nullable Column Property In Power BI.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[jakeryan56]

Another update. I tried deleting the field in question and now I am getting the same error but on a different column. 

[AllisonKennedy]

@jakeryan56 

I prefer the graphical view. Can you use the latest desktop version, creat a new page in the model view. Add the table in question to this new view. Right click, then add related tables. Finally enable show key columns at top of table and then send a screenshot of that please. And @ me in replies. Notifications aren't working well so I might lose the thread otherwise. 

[jakeryan56]

Thanks Allison.

I've done all of those things (I did realise I was using an older version) but still hasn't made any difference. I've also tried publishing in a new workspace and same issue still 

[AllisonKennedy]

@jakeryan56 is the report published in a shared workspace in service? Is there any possibility that the data model of the desktop is different to the service version? 

Can you try to republish the desktop report since it can refresh and see if that helps? 

If not, you may actually need to delete the dataset in service and republish. It has been over a year since I've tried but I used to have issues with changing the data model and not deleting the old version first. Some things like new columns wouldn't pull through to the service. 

[jakeryan56]

Yeah it is in a shared workspace but I already tried publishing to a fresh workspace and I get the same error. It's obviously something in desktop that I can't see and being pushed into the service. Any ideas? Settings, etc?

[amitchandak]

@jakeryan56 , can you share the data model diagram

[jakeryan56]

As you can see here this table "ClickposLineItems_Agg" does not have any active relationships using the field in question

[amitchandak]

@jakeryan56 , You have create a 1- Many relationship and you are getting null value in 1 side of the table. This can be master / dimesnion table

[jakeryan56]

No as I mentioned I have NOT created any relationships on this field. I have triple checked and this is not the case at all.

If this was true it wouldn't refresh in desktop