cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shaunwilks
Helper V
Helper V

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

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  

1 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!

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

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!

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. 

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!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors