- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Michael_Tucker on: New Card Visual inadvertently switching on values ...
- Gandalf12345 on: Copying Table with Filters is Ignoring Filters
- DNilsson on: March 2025 Update - Error fetching data for this v...
- Knox_DataMan on: Snowflake connector isn't showing views after Marc...
-
mattlee on: March 2025 update - Style preset error
- KrishnaKishoreK on: Conditional Formatting - Field Value not working a...
- jbongjbong on: Stretched App's Logo in Home Page "Recommended"
- pedrodr99 on: Button Slicer - Label - Color change not working
-
v-xiaoyan-msft on: Default Chart - Shape Map Is Not Working As Expect...
-
v-xiaoyan-msft on: Error (Exception from HRESULT: 0x80070002)
- New 7,873
- Needs Info 3,502
- Investigating 3,603
- Accepted 2,084
- Declined 38
- Delivered 3,964
-
Reports
10,150 -
Data Modeling
4,094 -
Dashboards
4,082 -
Gateways
2,103 -
Report Server
2,099 -
APIS and Embedding
1,951 -
Custom Visuals
1,770 -
Content Packs
521 -
Mobile
353 -
Need Help
11 -
Show and Tell
3 -
General Comment
2 -
Tips and Tricks
1 -
Power BI Desktop
1