How to hash a string column in power query

Question

I need a function that uses a native power query to hash a text string. I have tried using Web.Page with javascript but it never waits on the script to complete.

I would like it to return an integer.

What are some good methods to do this?

Answer 1

Using the algorithm provided in javascript javascript version, we can use list functions in power query to hash a string. The purpose is to convert a guid or file name to an integer hash to save memory.

let
    HashFunction = (input) => 
    let
        ListChars = Text.ToList(input),
        ListNumbers = List.Transform(ListChars, 
            each Character.ToNumber(_)),
        HashNumber = List.Accumulate(ListNumbers, 
            0,
            (state, current) => 
                Number.Mod((state * 31 + current), 9223372036854775807))
    in
        HashNumber
in
    HashFunction
enter code here

The function converts the string to a list of characters and then each character is converted to a number.

The calculation involves multiplying the current hash by a constant, adding the current number, and ensuring the result is a 32-bit integer.

Edit: The function above has a high collision rate for similar strings. This function works better, with a query called 'prime' defined elsewhere with a prime number in the sequence 13,131,1313...

let
    BKDRHashFunction = (input, seed) => 
    let
        ListChars = Text.ToList(input),
        ListNumbers = List.Transform(ListChars, each Character.ToNumber(_)),
        HashNumber = List.Accumulate(ListNumbers, 0, (state, current) => Number.Mod((state * seed + current),2147483647))
    in
        HashNumber
in
    BKDRHashFunction

The collision rate appears to be much better for this one.