cancel
Showing results for 
Search instead for 
Did you mean: 

PBI Connector unable to filter timestamp column

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?

Status: New
Comments