Summary
Data in ADX (aka Kusto aka RTA in Fabric) almost always has columns that contain datetime values like 2023-08-01 16:45 and sometimes timespan values like 2 hours or 36 minutes.
In this article I’ll describe how these values are represented in ADX in Power Query and in Power BI.
Notice that I don’t just say Power BI because timespan values have different types in Power Query and in Power BI.
Dates and times in ADX
ADX have to relevant data types : datetime and timespan.
In the attached pbix file, there are two basic columns, one is a datetime and one is a timespan.
Datetime values are assumed to be in UTC. They can be converted to local time using the datetime_utc_to_local function.
Here is the query that creates the data in KQL: (You can click on the web Hyperlink to run the query)
Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('ContosoSales')]
datatable(Date_and_Time:datetime, Span:timespan ,Spantext:string) [
datetime(2023-09-01), 2m,"2m",
datetime(2023-09-01 06:00), 2h,"2h",
datetime(2023-09-01 06:25:33),2d,"2d",
datetime(2023-09-01 23:55:12.1234), 30s, "30s",
datetime(2023-09-30),251ms,"251ms"
]
| extend Time_In_New_York=datetime_utc_to_local(Date_and_Time,"America/New_York")
Table0
|
Date_and_Time |
Span |
Spantext |
Time_In_New_York |
|
2023-09-01T00:00:00Z |
00:02:00 |
2m |
2023-08-31T20:00:00Z |
|
2023-09-01T06:00:00Z |
02:00:00 |
2h |
2023-09-01T02:00:00Z |
|
2023-09-01T06:25:33Z |
2.00:00:00 |
2d |
2023-09-01T02:25:33Z |
|
2023-09-01T23:55:12.1234Z |
00:00:30 |
30s |
2023-09-01T19:55:12.1234Z |
|
2023-09-30T00:00:00Z |
00:00:00.2510000 |
251ms |
2023-09-29T20:00:00Z |
Dates and time types in Power Query
The datetime columns in ADX are typed as Date/Time/Zone.
Changing the type to datetime will not change the value because the datetime is assumed to be in UTC .
The timespan column is typed as duration.
Date and Time types in the PBI data model
Power BI data types are described here.
The paragraph about Date/time types explains how Power Query types are mapped to the data model.
Some types are converted when moved from Power Query to the data model.
Date/Time/Timezone is converted to Date/Time and Duration is converted to decimal.
The behavior of timespan->duration->decimal needs some explanation.
A timespan of 12h is shown in PQ as 0.12:0:0 and in the model as 0.5.
For those of you with an Excel background, it will make perfect sense.
0.5 is half a day, the decimal is expressed as the decimal parts of a day.
For someone coming from KQL background it looks odd.
The timespan column can be converted in the query to seconds by multiplying it by 24*60*60 which is the number of seconds in 24 hours.