In ADX, how can I display large number in a human friendly way?

Question

In ADX(Kusto) Given a large number, say 1,111,111,111, I would like to format it in a human readable way, something like: 1.1B (for Billion).

How can I achieve this?

Answer 1

One approach could be using function with a swith-case to print the letter after the most-significant part of the large number.

Here is an example function to achieve this:

.create-or-alter function with(Doc="Format numbers into human readable form", Folder="danieldror/") format_numbers(n: long, precision: int = 1) { 
    case(
        n >= 1e15, strcat(tostring(round(n / 1e15,  precision)), 'Q'),
        n >= 1e12, strcat(tostring(round(n / 1e12,  precision)), 'T'),
        n >= 1e9, strcat(tostring(round(n / 1e9,  precision)), 'B'),
        n >= 1e6, strcat(tostring(round(n / 1e6,  precision)), 'M'),
        n >= 1e3, strcat(tostring(round(n / 1e3,  precision)), 'K'),
        n <= -1e15, strcat(tostring(round(n / 1e15,  precision)), 'Q'),
        n <= -1e12, strcat(tostring(round(n / 1e12,  precision)), 'T'),
        n <= -1e9, strcat(tostring(round(n / 1e9, precision)), 'B'),
        n <= -1e6, strcat(tostring(round(n / 1e6, precision)), 'M'),
        n <= -1e3, strcat(tostring(round(n / 1e3, precision)), 'K'),
        tostring(n)
    )
}

And executing it would give:

print format_numbers(1111111111)

which outputs:

print_0
1.1B

Note however, that this is using a rounding function, and for 1999999 will return 2M, which might not be desired in some cases.