March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
My PBI Connector has the following AstVisitor override for controlling SQL generated for timestamp values.
AstVisitor = [
Constant =
let
Quote = each Text.Format("'#{0}'", { _ }),
Cast = (value, typeName) => [
Text = Text.Format("CAST(#{0} as #{1})", { value, typeName })
],
Visitor = [
timestamp = each Cast(Quote(DateTime.ToText(_, "yyyy-MM-dd HH:mm:ss.fffffff")), "timestamp")
]
in
(typeInfo, ast) => Record.FieldOrDefault(Visitor, typeInfo[TYPE_NAME], each null)(ast[Value])
]
My datasource has a Timestamp column with fractional seconds precision but it appears that PBI expects timestamp values to have merely seconds precision.
When playing around in the query editor, the above function applied to timestamp value '2021-03-05 13:52:11.8310000' will return "CAST('2021-03-05 13:52:11.8310000' as timestamp)" but filtering on this value in PBI results in "where "time" = CAST('2021-03-05 13:52:12.0000000' as timestamp)" in the SQL statement issued to my data source. It appears that PBI is rounding off the timestamp value to the nearest second before it applies my AstVisitor function to it. As a consequence I get 0 results back from the SQL query because nothing satisfies the where clause.
Is it possible for PBI to allow for greater timestamp precision in situations like this?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.