Incremental refresh not working

[Anonymous]

Hello everyone, I'm stumped on getting incremental refreshes to work properly. I setup a report to use incremental refreshes following this article https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

When I opened the incremental refresh window I got the "cant verify if M can be query folded" error like the following 

When I published the report in a premium environment, incremental refresh did not properly work. Most likely due to query folding issues. Documentation is sparse on specifically how to properly leverage incremental refreshes. My understanding is that Power Query has to be used so that Power BI can use query folding to adjust the data start and end dates of the retrieved data. I don't understand exactly how Power Query needs to be structured to allow query folding. 

Here is my Power Query code 

let
Source = Oracle.Database("server_name", [HierarchicalNavigation=true]),
schema_name = Source{[Schema="schema_name"]}[Data],
table_name1 = schema_name{[Name="table_name"]}[Data],
#"Sorted Rows" = Table.Sort(table_name1,{{"DAY_DATE", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [DAY_DATE] > RangeStart and [DAY_DATE] <= RangeEnd)
in
#"Filtered Rows"

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Please kindly refer to this article:

https://powerbi.microsoft.com/en-us/blog/incremental-refresh-query-folding/ 

One important consideration to keep in mind is that incremental refresh works best with data sources that support query folding. Such data sources pass through some Power Query transformation steps, such as filtering and grouping, to the underlying data source to improve performance and avoid loading the entire dataset before filtering and grouping is applied. To determine if a specific step is “folded”, right-click the step, such as Filtered Rows, in the Applied Steps pane and observe if the “View Native Query” is enabled.

Although Microsoft doesn’t have an official list of data sources that support query folding and what steps are folded, typically relational data sources and data sources that can handle the SQL WHERE clause support query folding. Excel and text files do not. It’s important to check query folding because it the data source doesn’t support it, Power BI Desktop currently doesn’t prevent incremental refresh but it will load all the data before the filter is applied. Specifically, the query mashup (M) engine will apply the filter as it reads the rows before it gets loaded into the partitions. You might be able to mitigate this performance issue with non-foldable sources by applying the RangeStart/RangeEnd filter when the initial query is sent to the data source. For example, Dynamics Online supports a $filter clause that will work with incremental refresh:

= OData.Feed(“<endpoint url>/sales?$filter=CreatedDate ge ” & Date.ToText(RangeStart) & ” and CreatedDate lt ” & Date.ToText(RangeEnd)”)

[venal]

Please refer the below links.

https://powerbi.microsoft.com/en-us/blog/incremental-refresh-query-folding/

[Anonymous]

Following this article https://blog.crossjoin.co.uk/2016/08/02/another-way-to-check-query-folding-in-power-bipower-query-m-...

I can verify that query folding is True. Now Im really confused as to why "store rows where column "Day_Date" is in the last" part of incremental refresh is not working. I set it to only store 30 days of data and I'm all the data returned (over 10 years of data).