Format a number as currency in Azure Data Explorer

Question

I have a query that returns a decimal value. I'm looking for a way to format the value as currency. My current use case is limited to USD, but I'm sure others are looking for similar solutions.

I'd like to be able to go from "123456.789" to "$123,456.79".

Answer 1

There isn't a currency data type in Azure Data Explorer. This generates the value you're describing but it ends up as a string which probably isn't what you want.

print Value=123456.789
| extend Currency = round(Value, 2)
| extend Decimal = round(Currency % 1, 2)
| extend WholeNumber = toint(Currency)
| extend Segment4 = WholeNumber / 1000000000 % 1000
| extend Segment3 = WholeNumber / 1000000 % 1000
| extend Segment2 = WholeNumber / 1000 % 1000
| extend Segment1 = WholeNumber % 1000
| project CurrencyString = 
    strcat(
        '$',
        iff(Segment4 > 0, strcat(tostring(Segment4), ','), ''),
        iff(Segment3 > 0, strcat(tostring(Segment3), ','), ''),
        iff(Segment2 > 0, strcat(tostring(Segment2), ','), ''),
        iff(Segment1 > 0, tostring(Segment1), ''),
        substring(Decimal, 1))

The team takes feature requests at https://feedback.azure.com/forums/915733-azure-data-explorer

[Updated to handle commas for numbers ... but still has plenty of flaws/limitations.]