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.
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 06:00), 2h,"2h",
datetime(2023-09-01 23:55:12.1234), 30s, "30s",
| extend Time_In_New_York=datetime_utc_to_local(Date_and_Time,"America/New_York")
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.