Forum Discussion
Incremental refreshing when table doesnt have a date/time column
Hi
So this has been a huge headache for me. I need incremental refreshing to work on our super large tables but everything I've tried just doesn't work because whatever I do "View native query" gets grayed out and as I've read if it's grey then incremental refresh wont work. ( eventhough I can turn it on completely fine on the "Fields" panel. )
I have a masterdata dates table with proper date/time info seperately.
I have another table that has Date column but its a text format.
What I've tried: Make this Date column into date/time format = nope "view native query" gets grayed out.
I tried to make a similar looking column in our masterdata dates table ( native query is viewable and all good ) and then merge these 2 tables = nope it gets grayed out.
I can't write custom SQL to merge masterdata dates table with other tables because then incremental refresh wont work.
I have absolutely no more ideas because no I can't ask them to make a date/time table directly to other tables.
So is it absolutely only way for this to work is to make tabular model, combine those tables and work from there? Which means PBI is totally useless.
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.
4 Replies
Hi 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?
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.