kqlazure-data-explorermicrosoft-fabric

Converting binary to decimal in Kusto


Is there an elegant way to convert a binary string to its decimal value in Kusto? Preferably in the form of a scalar function, since the data I'm working with has multiple binary string columns, and all with different lengths.

I came up with two methods. The first is as follows: I mv-expand the string so it creates one row per character, I add a row number, and combine it with the binary string length to determine the bit value, then take the power of 2 of that value, and sum over all values by binary string:

let T = datatable(binary:string)
[
    "01000001000011010000001000100101",
    "11001001001000100111",
    "1010",
    "11"
];
T
| mv-expand bit = extract_all('(.)', binary) // One character per row
| order by binary asc // Serialize
| extend power = strlen(binary) - 1 - row_number(0, prev(binary) != binary)
| extend decimalValue = bit * pow(2, power) // strlen and row number determine the bit's value
| summarize BinaryToDecimal = sum(decimalValue) by binary

This only works if all binary values are unique, but can be modified by adding some constraints to the row number restart. But like I said, I have to work with a bunch of different binary string columns, so there's gonna be a lot of typing and that's harder to maintain.

The second one extracts all characters in the string to an array, and reverses it to determine bit value. Then mv-apply to multiply each bit with pow(2, array index), and sum over all values.

let T = datatable(binary:string)
[
    "01000001000011010000001000100101",
    "11001001001000100111",
    "1010",
    "11"
];
T
| extend bit = extract_all('(.)', binary)
| project binary, reversed = array_reverse(extract_all('(.)', binary))
| mv-apply reversed on (summarize DecimalValue = sum(toint(reversed) * pow(2, row_number(0))))

Is there a way to do this as a function?


Solution

  • Scalar function.
    Based on arrays manipulation.
    Ugly, but works

    let T = datatable(binary:string)
    [
        "01000001000011010000001000100101",
        "11001001001000100111",
        "1010",
        "11"
    ];
    let F = (binary:string)
    {
        let powersOf2 = toscalar(range i from 50 to 0 step -1 | summarize make_list(tolong(pow(2, i))));
        series_dot_product(parse_json(replace_string(tostring(extract_all('(.)', binary)),'"','')) , array_slice(powersOf2, -strlen(binary), -1))
    };
    T
    | extend DecimalValue = F(binary)
    
    binary DecimalValue
    01000001000011010000001000100101 1091371557
    11001001001000100111 823847
    1010 10
    11 3