ODBC PostgreSQL: The type of the current preview value is too complex to display�

[p24max]

Hi,

I'm trying to access a PostgreSQL 15.3 database with PowerBi Desktop 2.124.1805.0 64-bit (December 2023) using ODBC (psqlodbc 15 x64 driver).

Most of the tables are working, PowerBi Desktop can display a preview when adding new datasource, except for a few tables. Here I'm getting this error:

Preview.Error: The type of the current preview value is too complex to display.

When I change the query in advanced query editor and specify all columns manually, like shown below, it works without any issue (my changes to the standard query are marked bold):

let
Source = Odbc.DataSource("dsn=psql", [HierarchicalNavigation=true]),
psql_Database = Source{[Name="psql",Kind="Database"]}[Data],
public_Schema = psql_Database{[Name="public",Kind="Schema"]}[Data],
helpdesk_team_Table = public_Schema{[Name="helpdesk_team",Kind="Table"]}[Data],
SelectedColumns = Table.SelectColumns(helpdesk_team_Table, {"id", "write_date", ...}),
in
SelectedColumns

I noticed this issue persists only if I connect using ODBC. I tried to directly connect to PostgreSQL Server (without ODBC) which worked as expected, so I suspect an issue related to the ODBC. In my case I must use ODBC due to current infrastructure setup.

How can I fix this?

[Agucho76]

@p24max @v-zhengdxu-msft 

Hi there, your post was really helpful, thanks! I am facing similar issues and solved them so far with your suggestions. But now I face another issue:

My original dataset (lots of tables, relationships, measures, etc) was created using the PostgreSQL connector in Powerbi Desktop. Works great, but can´t be auto-refreshed in the service.

Changing the connector to ODBC seems to limit the way it was working and I´m not succeeding. 

QUESTION: is shifting to the ODBC connector the ONLY option if you want to have an auto-refresh set in the Powerbi Service through a gateway? Or is it possible to somehow set up the gateway with the PostgreSQL connector?

Thanks!!

[v-zhengdxu-msft · Accepted solution]

Hi @p24max 

If a Denodo view being imported as a data source into Power BI has more than 42 relations with other views, Power BI might display Preview.Error: The type of the current preview value is too complex to display. when accessing the data transformation window:

This error is due to a limitation in the Microsoft Power Query platform. In order to work around it, select the failing data source (query) in the data transformation window and access the advanced editor with View > Advanced Editor. Then edit the data source expression in M language adding the following property to the argument of the call:

CreateNavigationProperties=false

So your call would look similar to:

Source = Denodo.Contents(<dsn>, null, [CreateNavigationProperties=false])

This property will instruct Power BI not to try and generate navigation properties from the relationships registered for the Denodo view accessed in this data source. So if you need some of these relationships to be actually present in your Power BI data model, you will need to manually register them afterwards.

Link of how to connect to an ODBC data source from power query desktop:

Connect to an ODBC data source from Power Query Desktop

Solution of error of The type of the current preview value is too complex to display:

Loading data when a field in a Denodo view has more than 42 relations with other views.

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[p24max]

The workaround mentioned here: https://powerusers.microsoft.com/t5/Power-Query/Preview-Error-The-type-of-the-current-preview-value-... works, but really sad that PowerBi still has this bug..