Showing results for 
Search instead for 
Did you mean: 
coreyw Regular Visitor
Regular Visitor

Optional related entity - why 'Error'?

I am modifying a Query against an entity provided by an OData source.  An entity 'A' has an optional relationship to another entity 'B'.  i.e. 'A' has an (nullable) foreign key column and a navigation property to 'B'.


When I try and expand the 'B' navigation property in the Query Editor of 'A' to retrieve just one of 'B's properties, I see 'Error' for all those cases where the foreign key/navigation property does not point to anything in 'B'.  When there is a valid relationship link between 'A' and 'B', the navigation property works as I expect.


Is this by design?  I would argue that it should not be flagged as an error because the relationship is optional.



Microsoft v-ljerr-msft

Re: Optional related entity - why 'Error'?

Hi @coreyw,


I think it is the default behavior currently. And I would suggest you to submit your requirements on Power BI Ideas to improve this feature.Smiley Happy



Super User
Super User

Re: Optional related entity - why 'Error'?

Hi @coreyw


In my opinion this is by design in order for a relationship to work it needs to be able to identify each side of the relationship.


And what happens is that when there is a NULL value it cannot by any means make that identification, and thus this is why it fails.

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
coreyw Regular Visitor
Regular Visitor

Re: Optional related entity - why 'Error'?

Thanks for the comments.  If it is an optional relationship, it's not an error and shouldn't be flagged as an 'error'.  'NULL' or empty would make much more sense.


@GilbertQ,@v-ljerr-msft, I'm afraid this is a bigger deal than it looks like on the surface.


The problem I have with this is that when you save the query, an error dialog comes up, and then it throws AWAY all the records with these so-called errors.


This should not be the default behavior.  It's throwing away semantically valid records. It's a data loss bug, and a serious one.


The maddening part is that Power Query Excel does NOT throw away the error records, so there is no consistancy.

Helpful resources

New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)