Forum Discussion
Incremental refreshing when table doesnt have a date/time column
Hi Anonymous ,
Please refer this document: https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#query-folding
Query folding:
Given the various levels of query folding support for each datasource, 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. If you see this warning and want to check that the necessary query folding is occurring, you can use the Query Diagnostics feature, or trace queries received by the source database.
As the warning shows, since the folding is working and fine, you can ignore it but not recommend becasue it can cause incremental refresh to be very slow, and the process can run out of resources either in the Power BI service or in the on-premises data gateway if used.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community SupportHi Anonymous ,
Please refer these pictures about transformations that can achieve and prevent folding:
Under these limitations, you could need to change the data type from the data source instead of in power query to use incremental refresh. More details about query folding, you can refer the document:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the reply. I think I already got the data folding problem figured out but theres still 1 more thing I'm worried about.
As on this picture you can see folding is working, I can view native query just fine:
But when adding incremental to a table it still shows me a warning:
Can I just ignore it since folding is working and fine?
- v-yingjl
Hi Anonymous ,
Please refer this document: https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#query-folding
Query folding:
Given the various levels of query folding support for each datasource, 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. If you see this warning and want to check that the necessary query folding is occurring, you can use the Query Diagnostics feature, or trace queries received by the source database.
As the warning shows, since the folding is working and fine, you can ignore it but not recommend becasue it can cause incremental refresh to be very slow, and the process can run out of resources either in the Power BI service or in the on-premises data gateway if used.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SofBLAdvocate II
Hi, the trick is to do a conversion of the datetime to the format of the table you try to filter.
this is documented in https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure#convert-datetime-to-integerbut beyound integer you can convert to text as well.
So filter the sourcetable with a converted format by using a function that transforms the RangeStart and RangeEnd to the format of the sourcetable. Once you start using functions in powerquery the options are endless.