The Incremental Refresh Date Filter using RangeStart & RangeEnd Parameters not working as expected

[MangeshVC]

Hi All !

I am trying to configure the Incremental Refresh at Power Query. When filter is applied using the two parameters RangeStart, RangeEnd on the specified date column a very weird backend query got generated. In the Where Clause it is expected to show date column is getting filtered by the two parameters (like Date >= RangeStart & Date < RangeEnd), instead it is showing a filter on ID column present in the dataset. 

The column on which the parameter filter applied is a date time column.       

I have successfully implemented incremental refresh for a different table. But facing issue with current table  from same source(SQL Server).

What could be reason? Please help !

[Poojara_D12 · Accepted solution]

Hi @MangeshVC 

The issue might be caused by incorrect query folding or mismatched data types. Here's how to resolve it:

1. Right-click in Power Query on the applied steps and ensure the "View Native Query" option is enabled. If not, the query is not folding properly.

2. Ensure the column you are filtering (DateTime) is set to the correct Date/Time data type in Power Query.

3. Reapply the RangeStart and RangeEnd filters on the DateTime column explicitly in Power Query.

4. If the ID column is calculated or used indirectly, it may interfere with query folding. Ensure the filtering is directly on the date column.

5. Verify that the M code applies filters directly to the DateTime column using RangeStart andRangeEnd. Adjust if needed.

If the issue persists, the source query (SQL Server) might require optimization to support proper query folding.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS