Why my Power BI Incremental refresh isn’t working? (Hint: Native SQL Query)

Sometime back I was working on a requirement to extract millions of records from a datawarehouse system for Power BI reporting. I was using native SQL query with multiple table joins and where clauses. As the records were in millions, implementing incremental refresh was the best choice to avoid reloading the entire data on every refresh action plus this would reduce the time to reload as only subset of data will need to reloaded. While implementing incremental refresh, I encountered the query folding issue like below

As recommended in the message, you can’t implement incremental load unless you have resolved the query folding issue so I couldn’t move ahead without finding the root cause of it. After some research, I was able to unlock the problem. I found that you can’t use native SQL query for incremental load. This can be confirmed by opening Power Query editor and right click on the gear icon of the last step of the query. You will see the view native query is disabled which means there is an issue.

The solution to this problem is to avoid using native query when implementing incremental refresh and instead get data from table by navigation. Below is an example .

If you have table joins then it’s recommended to create a view with all the SQL logic (including table joins, where and and clauses) in it and then use “Get data” from the view using navigation.

Please follow Microsoft link here to find step-by-step instructions on how to implement incremental refresh.

Additional Information

• Make sure to use “RangeStart” and “RangeEnd” parameters. These are reserved keywords.
• Make sure the datatype of the above parameters is the same as the column on which custom filter will be applied.
• You cannot download Datasets that have incremental refresh implemented.

Hope this post helps someone to unlock this issue.