How to unfold queries ?

[vikramapandian]

Hi Team,

I have to turn on Incremental refresh, but with a folded query it doesn't work. The source for the query is from cosmos DB i.e the data is in the form of a flat structure. By default, the navigate to query option is grayed out in the 1st step itself. Would be of great help, if anyone who knows how to solve this can provide a solution. Thank you!

//Vikram

[v-jayw-msft]

Hi @vikramapandian ,

Actual Incremental Refresh is designed for data sources that support query folding. If Power BI Desktop is unable to confirm if the datasource supports query folding, the warning will be shown in the Incremental refresh policy configuration dialog. If you see this warning and want to verify the necessary query folding is occurring, use the Power Query Diagnostics feature or trace queries by using a tool supported by the data source, like SQL Profiler. If query folding is not occurring, verify the filter logic is included in the query being passed to the data source. If not, it's likely the query includes a transformation that prevents folding.

Refer:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#supported-data-s... 

Best Regards,

Jay

[lbendlin]

It may well be that it was designed that way. But that was a silly design not rooted in the reality of most data load processes.

Most of our incremental refresh implementations run without query folding, and they run very well. 

[vikramapandian]

Here is the IR configuration screenshot,

Not sure if the warning can be ignored.

[lbendlin]

Yes, that warning can be ignored.

Next step is to consider the impact of your refresh choice. Even though you say "last7 days" the Power BI service will create up to 61 (!!!) Partitions. I recommend you switch to "last 1 month" to create only 2 partitions.

Then start refreshing the dataset and observe partition refresh dates in SSMS or DAX Studio etc.

[vikramapandian]

Hi,

Here is the screenshot of the power query,

[lbendlin]

That looks ok.  Next show the settings in Power BI Desktop for the incremental refresh partitioning.

[lbendlin]

"I have to turn on Incremental refresh, but with a folded query it doesn't work"

That is inaccurate. How did you come to this conclusion?

Provide more details about your setup. Show the Power Query code that includes the RangeStart/RangeEnd filters.