Optional related entity - why 'Error'?

[coreyw]

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.

[GilbertQ]

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.

[coreyw]

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.

[v-ljerr-msft]

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.

Regards