Polling Expression Format

[tac]

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

[Anonymous]

@GilbertQ @tac  I am also trying to use the Polling Expression to update the historical partitions where delete or update had occurred. But in my case the polling expresson doesn't seem to work. It is not doing anything after I refresh my dataset. Can you let me know what could be the issue?

[tac]

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).

[GilbertQ]

Hi there,

The initial issue could be during the first evaluation it is not returning anything?

Secondly the reason that for each partition call or query it first runs the top 1000 is because it always checks the table to make sure it has got the expected columns in the table before completing the entire query only to realize later that there are columns missing. This is by design.

[tac]

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...