Odata - Enabling Native query - Native queries aren't supported by this value. Details: [Table]

[GermanAravena]

Hello,

Trying to Enable Native queries but getting the following error

Native queries aren't supported by this value.
Details: [Table]

The video below makes it look simple but not sure what I'm doing wrong

https://www.youtube.com/watch?v=8hjdOCni_ZY 

I'll use the northwinds dataset as an example of how i'm getting the error:

[Daryl-Lynch-Bzy]

@GermanAravena  - are you trying to connect to an SQL server that is managed by your DBA?  If so, why are you using an OData connection to a public source like "services.odata.org".  Please note, the OData will not support query folding or the advance API connection that Miguel is describing.  In this scenario, you will be copying the entire table from the northwind database before applying the filter date.  If you are testing this example, I would suggest using a SQL database source, or set up a basic SharePoint example like Miguel's. 

Note Miguel's example is not technically query folding, it is just a lazy evaluation example.  The Range Start and End are turned into a filter to reduce the number of files retrieved from SharePoint files.  He is taking advantage of the SharePoint's API to create a url string with additional filter.  The first time he runs this issues 3 URL requests from SharePoint, when the incremental load is enabled, only one URL request is issued (a lazy evaluation).

[GermanAravena]

@Daryl-Lynch-Bzy  The source of the data is SAP.

I will review your notes with a colleague of mine, but I'm finding as you said - You have to populate a SQL database to get this to work.

[Daryl-Lynch-Bzy]

Hi @GermanAravena , In the example that Patrick shows, there is a connection to SQL Server data source.  This data source supports Query Folding by default.  You connection is using a OData connection, so it is possible that the OData data source may not support Query Folding.  In this situation the "services.odata.org" northwind does not support query folding.  Please note the "Query Folding" relates to whether the Client (i.e. your Power BI Desktop) or the Server (i.e. the Odata datasource) is using the CPU to process the query.  If the data source is an SQL Database, the database CPU can be used to process the query then return the results.  In your situation, the OData feed is not hosted on a database.  It is technical just a Blob storage.  There means that you need to return all the data from the Datasource to your Local Client to process the query.  I hope this help you understand.

[GermanAravena]

Hi Daryl,

Thanks for getting back to me, much appreciated!

Ultimately I am trying to enable the incremental refresh feature built into the powerbi service using an oData source.

I'm finding multiple posts and blogs that indicate it should be possible but that enabling native query is a requirement.

If I'm understanding correctly, this might be a setting/permission that my DBA needs to enable?

https://community.powerbi.com/t5/Community-Blog/Query-Folding-in-PowerBI/ba-p/1853138 

https://www.thepoweruser.com/2020/01/19/incremental-refresh-for-files-in-a-folder-or-sharepoint-powe...