Query Folding with Complex Native Queries

[datapal04]

Hello,

I have several native queries I'm using to get data into Power BI from SQL Server (using SQL statements - advanced option). Some of my native queries are complex - some include joins or performing counts. I'm unable to achieve query folding (at least according to this method here) - I get an error saying the query cannot be folded. 

I would like to know if queries as complex as this can achieve query folding? I'm finding the only way to do this effectively is to have a view created and having a "select *" to get that data. Ultimately, I am trying to achieve incremental refresh. Any advice would be great!

[ppm1]

If you can't get folding to occur with Value.NativeQuery, you can always create datetime parameters called RangeStart and RangeEnd and concatenate those into your SQL statement WHERE clause too. This video shows an example - (1) Natives Queries or ODBC with Incremental Refresh in Power BI - YouTube

Pat

[datapal04]

Hi @ppm1 , I tried following the video you shared by creating a dataflow but I run into the error below while trying to authenticate for it. My database is on-prem... could this be a reason why I am unable to achieve query folding for incremental refresh?

[ppm1]

Can you get it to work in a query in Power BI Desktop? Is the issue only when you try it via Dataflow? If so, do you have a gateway set up?

Pat