Timespan/duration values in KQL, Power Query and Power BI

 

Summary

Time values are called Timespan in KQL, duration in Power query and time in Power BI.

Naming are not the only confusion, a duration column in Power Query is shown as a decimal number in Power BI and not as a time value.

In this article I’ll show how a timespan aka duration aka time can be used when querying data from Kust aka ADX aka KQL database

 

In KQL

 

One of the datatypes in Kusto is timespan. If you subtract two datetetime columns, the results is a timespan.

Execute in [Web] [Desktop] [cluster('kuskuseus.eastus.kusto.windows.net').database('Kuskus')]

print Span=datetime(2024-3-10 22:30)-datetime(2024-3-9 08:50)

PrimaryResult

Span

1.13:40:00

 

Execute in [Web] [Desktop] [cluster('kuskuseus.eastus.kusto.windows.net').database('Kuskus')]

print Span=datetime(2024-3-10 22:30)-datetime(2024-3-9 08:50)

| getschema

getschema

ColumnName	ColumnOrdinal	DataType	ColumnType
Span	0	System.TimeSpan	timespan

 

The timespan value is formatted by default as ddd.hh:mm:ss

The internal value of a timespan value is an integer with the number of ticks when 1 second is 10,000,000 ticks.

A literal timespan can be specified as timespan(2.2:2:2) – 1 day 2 hours 2 minutes 2 seconds.

More commonly timespan literals are specified as 20s, 3m , 4h, 5d, 2.5d for 20 seconds, 3 minutes , 4 hours ,5 days and 2 and a half days.

Timespan literals are very commonly used when filtering datetime values as in:

 

| where Timestamp > ago(3h)

Math using timespan values

1m+1m=2m (timespan result)

3h-20m=2hours and 40 minutes

5m*5=25 minutes (timespan result)

 

3.5h/1m= 210 (real result)

 

3m/7s=25.714285714285715 (real result)

Other math operations like multiplying two timespans or dividing two timespans are illegal.

 

Timespans can be compared with == > < >= <= between

 

 

Power Query

 

A timespan column in KQL is recognized by Power Query as a #duration column.

Durations are displayed in PQ exactly as in KQL.

3.07:59:59.9971200 is 3 days, 7 hours, 59 minutes, and 59.99712 seconds.

Subtracting two datetime columns in PQ will generate such a duration column.

You can use a bunch of duration functions as listed here

 

Power BI

 

Here is where the problem starts.

Power BI doesn’t have a type compatible with duration or timespan.

This is mentioned specifically here

Any such column appears in Power BI as a decimal column.

The value of the decimal column is the duration value in days.

The value used in the previous example is shown as 3.33333 days.

If you try to display this value in a visual, it may seem to just work but pretty soon , you’ll get errors.

The problem is that what power bi sees as a number is a timespan in the KQL world.

When PBI tries to compare this value with a number, the Kusto engine will produce an error complaining that a timespan cannot be compared to a number.

 

Solution

 

The only working solution is to convert the duration to an integer in PQ and not to allow Power BI to do the conversion behind the scene without notifying the AzureDataExplorer connector.

When you convert the duration column to number , the value you get is in ticks.

There are 10,000,000 ticks in a second.

If you want to see the value in days you’ll have to divide the values by (10000000*60*60*24).

This numeric value is known to be a number as part of the result coming from the backend.

Any comparison or any math operation done with this value will work as expected.

In the attached example I added both a substraction created as a duration and another column converted to decimal type in the query.

If you try to filter the duration column , you’ll get an error

 

 

If you filter on the decimal column it will work just fine