Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi community,
Anyone here has already experienced to override the polling expression when having incremental refresh and cdc enabled?
I've tried to update the original expression:
"pollingExpression": "let MaxTimestamp = List.Max(#\"Main Fact\"[Timestamp]), accountForNull = if MaxTimestamp = null then #datetime(1901, 01, 01, 00, 00, 00) else MaxTimestamp in accountForNull",
By replacing the source query (#Main Fact) to an alternate query containing basically the latest modification date of each partition. This query is filtered by parameters RangeStart/RangeEnd to make sure it returns only one row per partition call. I have saved this with success through an XMLA call with SSMS.
Then, when running a refresh from powerbi.com, the refresh got stuck for 10/15 minutes and nothing happens. No sql query is sent against the server. After a while, I'm getting the following error:
COM error: Microsoft.Data.Mashup, Value cannot be null. Parameter name: json.
Any experience on this? idea/suggestion?
Additionally, when profiling the sql queries sent against the server, I have noticed (with the original pollingExpression setup) there are queries with top 1000 clause that are sent to the server, one per partition. Any idea why such queries are sent to the server ? Note this happens even when refreshing from powerbi.com, this is not related to the schema evaluation happening in PowerBI Desktop.
Thanks for your support
Cheers
Hi @Anonymous,
Difficult to diagnose.... Using the SQL profiler would be helpful for you in order to understand if there are queries sent against the server (or any other similar profiler depending on your datasource).
Hi,
Thanks for your suggestion!
I don't think the first execution returns null, as the M expression prevents to return null value by testing and returning a default datetime value.
Note, It seems returning for each execution of the polling expression the same error, as in the output, the error message is concatenated several times, maybe one per partition...
Is there any proper documentation about the polling expression???? It seems quite poor for the time being. Nothing really precise about the usage of this.
The top 1000 behavior really su***.... This is really a poor design in my opinion... What if we have to pay per query? A top 1 would be enough, and only once for all partitions! Really a bit disappointed by this behavior. I hope Microsoft could do something to improve, as this is really annoying to have such queries executed for.... nothing...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.