Power BI Kusto Direct Query generates malformed datetime KQL
I have a Microsoft Fabric KQL database with an EventTime column of type DateTime containing timestamps, an example value "2021-04-20T08:03:20.3230723Z". I succesfully added the database as a direct query data source on Power BI, everything else seems to work but trying to create a slicer with EventTime fails with this:
.
I can see that the generated KQL sent to the database looks like this:
["ExpandDataForPowerBI"]
| project ["EventTime"]
| extend ["o0"]=["EventTime"]
| summarize by ["EventTime"], ["o0"]
| extend ["t0_0"]=iff(isnotnull(["o0"]), todatetime(["o0"]), todatetime(datetime(1899-12-28 00.00.00.0000000))),["t1_0"]=iff(isnull(["o0"]), toreal(0), toreal(1))
| order by ["t0_0"] asc,["t1_0"] asc
| project ["EventTime"]
| limit 101
| project-rename ["c1"] = ["EventTime"]
The issue is with the hour/minute/second separators being "." instead of ":" in e.g. datetime(1899-12-28 00.00.00.0000000).
Is there any way to fix this or is this a bug in Power BI?
Example of visual working:
After adding slicer, it breaks:
Update
Creating the Power BI report online in the browser does work. So it might be related to my region (Finnish)
The issue was locale. When creating the report in desktop Power BI, my locale was "English (Finnish)". Changing it to English (United States)" Fixed the problem. I think this is a bug in desktop Power BI, and the KQL queries should work regardless of locale, given that KQL only supports one type of datetime format.
