This article is contributed. See the original author and article here.
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.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments