cannot folder expression - data connector and PostgreSQL

[xsgao_pbi]

I want connect to Postgres ODBC data source with direct query enabled. So I took this sample project, https://github.com/Microsoft/DataConnectors/tree/master/samples/ODBC/SqlODBC, point Config_DriverName variable to "PostgreSQL Unicode(x64)" (name of my psqlODBC driver, version 03.51), and set ConnectionString based on a working ODBC DSN.

I am using Power BI Desktop, Version 2.61.5192.601 64-bit (August 2018), and there is no problem to add one table to my Power BI report.

But whenever I put a column into a table (or any other viz), I got this error:

OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.

And it happened to any column, varchar, number or date.

My original post on github: https://github.com/Microsoft/DataConnectors/issues/143

[v-qiuyu-msft]

Hi @xsgao_pbi,

Which modification you made in SqlODBC sample project? Would you please share the whole project files with us? 

Best Regards,
Qiuyun Yu 

[xsgao_pbi]

Here is the link to my project on github: https://github.com/Microsoft/DataConnectors/files/2329590/SenturusDataConnector2.zip

[v-qiuyu-msft]

Hi @xsgao_pbi,

Thanks for your project. I gave reported this issue internally: CRI 82027633. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @xsgao_pbi,

I got information below: 

Folding and DQ of custom connectors is not something that we support. If the customer wants to participate in the folding stack, they have to implement Table.View, instead of wrapping PostgreSQL connector. 

Best Regards,
Qiuyun Yu 

[xsgao_pbi]

We got this fix by adding SQLGetTypeInfo and SQLColumns functions. PostgreSQL ODBC driver returns some duplicate types with same type names, and making them unique fixed the problem - no "cannot fold expression" errors now.

[sowrk]

Hi @xsgao_pbi,

I am running into the same problem that you encountered. I created a custom connector to connect to postgres ODBC datasource with direct query enabled.

I get the 'Cannot fold expression to the datasource" error. But once I add the SQLGetTypeInfo and SQLColumns functions, not even able to load data from a table in PBI desktop due to an error - "Expression error - Token literal expected"

Could you please elaborate on your solution. Should the SQLGetTypeInfo function be modified to return only unique types? And any changes required in SQLColumns function?

[Anonymous]

Even i am running into the same problem.

I have tried using Table.Distinct to remove duplicate type name, but still getting the same problem.

@xsgao_pbi  can you please share how you got it working?

[xsgao_pbi]

Please refer to https://github.com/microsoft/DataConnectors/blob/master/docs/odbc.md, Section "SQLGetTypeInfo using a static table".

You don't have to support/map all PostgreSQL data types, only those you need to support.