Daniel Little on 03 Jun 2020 12:40:54
When building a custom connector table relationships are known ahead of time. However there is no way to declare table relationships so they can be imported. This leaves users having to fix up the relationships manually.
I propose the following options:
- The ability to add foreign keys to a table
- Or the ability export table relationships (along with the Navigation table).
There are some pre-existing discussions on GitHub, as the documentation attempts to provide an example of doing this. However the sample code does not actually address this issue (because the approach is flawed, and it's not actually possible).
https://github.com/microsoft/DataConnectors/issues/97
https://github.com/Microsoft/DataConnectors/issues/150
https://community.powerbi.com/t5/Power-Query/Custom-data-connector-relationships/td-p/537228
- Comments (3)
RE: Enable Custom Connectors to explicitly declare table relationships
Even there is a sample that shows how to build Nasted and Implicit rels
https://github.com/Microsoft/DataConnectors/tree/master/samples/Relationships
It seems that it does not work when one of the table (Details) is empty when the model is created.
I am building a connector for a system where it is not expected that all entities will have data. If the model is build when some entities are still empty then the Power BI fails to discover the relations even with the Nasted example.
RE: Enable Custom Connectors to explicitly declare table relationships
There is really no way to specify relationships in tables.
RE: Enable Custom Connectors to explicitly declare table relationships
This could also be useful for all data imports. For example you could include a "relationships" csv to define the field relationships when using the csv import functionality.