This table uses directquery and cannot be shown - Workaround?

[sp_mike]

In PowerBI desktop I "connected" a table with directquery. Now I added a custom column and want to see the result in the data view.

However I get "This table uses directquery and cannot be shown" 

Is there some other way to see data, including my custom column?

[artemus · Accepted solution]

The ability to add custom columns in a direct query depends on the ability for the query to fold. If your Add column operation is too complex to be handled by the connector it won't work. To avoid this, try adding the custom column in power query instead (the query editor)

If your using SQL try right clicking a step and see if "View native query" is not grayed out then it is folding just fine.

If your using another connector that supports folding (e.g. kusto) then you would need to add a custom step of Value.Metadata(<PreviousStep>)[QueryFolding] to see if it folds or not.

[francescom]

I have a similar problem.

The DirectQuery table is correctly folded (check both Value.Metadata and the native query).

Since many PostgreSQL are having similar issues, I would like to have an update from Microsoft what support Power BI offers for using DirectQuery with PostgreSQL databases.

Thanks in advance.

[artemus]

I can't give you an official answer (I work in Azure), but I will say that there is active work in fixing folding issues in PostgreSQL, wheather the current fixes make it into production, if they will help solve your issues, or when they will be released, I would have no idea.

A lot of the problem has to due with, while the query by itself might fold, the additional dax queries on top of it might not. This could include even the simplist of dax queries which happened during model validation like distince count of values in each column that there is a relationship too, or it put as a filter/legend.