Cannot set up incremental refresh due to not being able to apply query changes in PBI Desktop

[Anonymous]

Hi,

Problem: I am unable to set up incremental refresh due to the fact that I'm not able to apply query changes in PBI Desktop. Without being able to apply query changes PBI Desktop says in the incremental refresh pop-up that "Unable to confirm if the M query can be folded."

Applying the query changes fails, because the source data set is too heavy for importing to PBI Desktop as a whole, but I have confirmed it not to be too heavy to import more than 200k of rows. The Desktop tells me that query timeout expires when applying the changes I set up in Query editor as instructed by: https://docs.microsoft.com/fi-fi/power-bi/service-premium-incremental-refresh#feedback

I have tried the following, which doesn't seem to work (unchecking the "Include in report refresh in Query Editor): https://community.powerbi.com/t5/Desktop/Apply-query-changes-without-refresh-data/m-p/588967#M279324

Facts I know to be true:

1. I have no control over the timeout expiration controls of the source

2. I know for a fact that the source supports query folding

3. I know that the source SQL view is heavy on scripts (contains joins etc.) but I have tried playing with subsets of the data to know that the source should be importable but it is heavy and large (estimated 250 mb if it could be imported as a whole to PBIX file)

4. I have tested to be able to import more than 200k of rows from the dataset without any issues using filters in the queries.

[v-yuta-msft]

@Anonymous ,

Please check if there's filter logic in your query.

It's recommended that verification is performed to ensure the filter logic is included in the source queries. To make this easier, Power BI Desktop attempts to perform this verification for you. If unable to verify, a warning is displayed in the incremental refresh dialog when defining the incremental refresh policy. SQL based data sources such as SQL, Oracle, and Teradata can rely on this warning. Other data sources may be unable to verify without tracing queries. If Power BI Desktop is unable to confirm, the following warning is displayed.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuta-msft ,

The warning message I'm getting says "Before you can set/modify incremental refresh you need to apply pending changes in your queries..."

Applying the filter is actually last step in the process I'm able to do correctly and I can see the appropriate WHERE -clause in the native query window. But after that I cannot apply the pending changes due to the imported data set being so large, which is why I chose to try incremental refresh in the first place. The underlying data set is so large or computationally heavy that the Power BI desktop query fails in timeout.

Is it true I need to be able to import the whole data history with Desktop before it can be taken to service for incremental refresh? I need the history from the whole data set and I know it can be imported in parts but not as a whole due to it being quite computationally heavy for the source.