Incremental Refresh - Query Folding - How to Tell - PowerQuery vs PBI Desktop

[shaunwilks]

Did a deep dive into Query Folding for the purpose of Incremental Refresh.  Read through here in addition to other resources

https://docs.microsoft.com/en-us/power-bi/guidance/power-query-folding

https://docs.microsoft.com/en-us/power-query/power-query-folding

These resources clearly suggest that query folding is supported if you can right click the query and View Native Query. 

I can right click on my Query and "View Native Query" in the Query Editor - TICK

When I right click the Query In PBI Desktop to configure Incremental Refresh I get the warning ....

"Unable to Confirm if the PBI Query can be folded." - If thats the case why am I able to right click and view the native query in the query Editor ?? Straight away I am being told two different things here. 

Query Editor says it folds and shows me the SQL Query being made - The Desktop suggests it cant fold.

1. Would be great if these were able to be consistent and use the same rules to determine folding.

Then there is the restrictive nature of Query folding and incremental refreshes

Queries Can't use Native query, Queries also cannot include...

-Merging queries based on different sources.

-Appending (union-ing) queries based on different sources.

-Adding custom columns with complex logic.

-Adding index columns.

-Changing a column data type.

Firstly when PowerBI Desktop imports data from a PBIT what is clearly a date field is formatted as "text" field.

I want to change the data type to date inside the query.  But doing so breaks the folding and incremental refresh. 

So I dont change the date format inside the query and instead I format the field as a "Date" inside the PBI Desktop.

That works - terrific - but do another refersh or export to PBIT and load a new database in and its back to being Text again.

 2. Its really a no win situation here as I cant use either means if I want to distribute reports AND use Incremental Refresh

FInding the whole thing incredibly messy and tedious  

[v-kelly-msft]

Hi  @shaunwilks ,

Check below reference for help:

https://docs.microsoft.com/en-us/power-query/query-folding-basics

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[shaunwilks]

I have read that... It also says

"Checking the View Native Query option is always recommended to make sure that your query can be folded back to the data source. If your step disables this option, you know that you've created a step that stops query folding."

I have a data source where View Native Query works = Folding is working..

I dont have a problem getting the query to fold.

But then back in the editor setting up incremental updates on the VERY same query tells me

"Unable to Confirm if the PBI Query can be folded." 

So the query editor tells me its folding. But the Editor is warning it cannot be confirmed.

Why cant both applications be consistent ?? 

What tests does the incremental refresh perform to show that warning, that the Query Editor does not ?

Clearly they are different checks and only causes confusion for thos trying to configure incremental refresh. 

[v-kelly-msft · Accepted solution]

Hi  @shaunwilks ,

After checking,if View Native Query option is enabled,it means that it is highly likely to have query folding,but it's not definite.When you do some transformations that SQL can't parse,you will get such notification to advise you that the query might not be folding back,but this is also not definite,you'd better use other tools such as SQL profiler or Azure data studio,etc to check whether the query folding is taken place.

In below vedio,Patrick tells how to check whether query folding is happening:

https://www.youtube.com/watch?v=QEFze-LdLqo

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!