Expand record column option not available even with foreign key defined in database

[mattcarter865]

I am connecting to a PostgresQL database (DirectQuery). The table I am querying has multiple foreign keys defined however Power Query is not detecting any related tables for it. So I don't get the option to expand the record column. I've used this feature on other databases with foreign keys defined.

I confirmed that I have the "Include relationship columns" option checked. 

I know I can work around it by adding the lookup tables then doing a merge, but it would be a lot more convienent to use the expand column feature. Just not sure if that feature is unsupported for Postgres or if there is some other option that I am overlooking.

[v-yingjl]

Hi @mattcarter865 ,

First make sure the foreign key works well in your databased.

When connecting to the database, to select related tables, not only enable 'Including related columns' in the connector page but also enable 'Select related tables' in the Navigation page.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mattcarter865]

I can confirm foreign keys are valid and work. I tried the "Select Related Tables" button and it did not work. It did not find any related tables. I'm guessing for the same reasons the expand record column option is not available in the first place. 

Also, I have used the expand record column option without using the "Select Related Tables" button in different database engines (e.g. Oracle).

Is your example using PostgresQL?

Thanks

[v-yingjl]

Hi @mattcarter865 ,

Have re-created two sample tables in PostgreSQL DB again to test:

Category_Category

The foreign key between two tables is like:

When connect to each of these tables separately in power query, both of them can expanding columns without choosing 'select related tables':

When you connect to PostgreSQL, have you seen the expanding column in the Navigator page?

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mattcarter865]

Must be something with this particular postgres database then? I don't see the same things you see in the Navigator dialog. 

[v-yingjl]

Hi @mattcarter865 ,

I don't think so. As far as my test, only create two tables and the foreign key without any other steps in the database.

"Must be something with this particular postgres database then? I don't see the same things you see in the Navigator dialog. "

Perhaps the foreign key still does not work in the database.

What's the version of your power bi desktop and postgresql db, maybe you can try to update to the latest version to check it again.

My testing version:

Power BI Desktop: 2.94.921.0 (June 2021)

PostgreSql: 13.3 Windows x86-64

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mattcarter865]

I'm using PBI June 2021

Postgresql: 11.9 hosted on AWS (This is a company db, so I don't have control of the version)

I'm sure the foreign keys work. Using DBeaver, I can see the FKs and navigate them (i.e. click on a FK value and it opens the referenced table). Also, we would probably have production issues if they didn't.

[v-yingjl]

Hi @mattcarter865 ,

Have tested again on PostgreSQL 11.9 in my side with the same steps and got the same result. Both of tables related by the foreign key can be expanded columns whether the connection mode is import or direct query.

If this issue is urgent for you, I would suggest you to create a support ticket here for further support when having a Pro license.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.