PowerBI direct query connection to PostgreSQL error. OLE or ODBC error: [Expression.Error] We couldn't fold the expression to the data source
I just connected to PostgreSQL with a Direct Query storage mode for the first time today, and ran into this error as well. Like you, the connection and everything else seemed to work fine, but then threw an error when trying to use visuals that worked fine when using the Import storage mode.
My error read: OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression. I assume that the last sentence was added in a later version of Power BI.
Carl de Souza explains Query Folding here and it appears that Direct Querying attempts to fold as much of the analysis into the Native Query as possible. I hadn't specified any query when I first pulled in the data, but once I did the error disappeared.
For any who don't know how to do this, after clicking 'Get Data' (or 'New Source' if already in the Query Editor) and choosing PostreSQL database, make sure to add a SQL statement in the Advanced options section.
For me, a simple SELECT * FROM my_schema."my_table_name" was sufficient.