Generate a Random Alphanumeric String in Power Query

6 Comments

  1. Thanks Chris 🙂
    My approach to the problem:
    let
    ValidCharacters = Text.ToList(“ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456879”),
    StrLenght = 32,
    Result = Text.Combine(List.Transform( {1..StrLenght}, each ValidCharacters{Int32.From(Number.RandomBetween(0, List.Count(ValidCharacters)-1))}))
    in
    Result

    Regards

    1. Author

      That’s great, Bill! It is shorter and easier to understand than my solution. Do you mind if I add it as an update to my post with credit to you? Thanks for sharing your solution.

      1. I re-thought my solution and I am pretty sure that I can improve it. Why? Because I want to get. better probability for the last character of the ValidCharacters
        Because the last character from a ValidCharacters is almost impossible to receive.
        So I decided to change the “Result” step to this below.

        Result = Text.Combine(List.Transform( {1..StrLenght}, each ValidCharacters{Number.IntegerDivide(try Number.RandomBetween(0, List.Count(ValidCharacters)-0.01) otherwise StrLenght -1, 1)}))

        Of course, I do not mind the post update 🙂

        Regards

        1. Author

          Thanks Bill. I’ll update the post with a recommendation that people check out your solutions in the comments, that way your comments speak for themselves.

          Out of curiosity, if Power Query’s random function is truly random, why would the probability be different for the last character compared to any other?

          1. I have been not precise enough. From this construction
            ValidCharacters{Int32.From(Number.RandomBetween(0, List.Count(ValidCharacters)-1))}
            we receive 2 times smaller probability to get “A” or “9” than other characters (because of Int32.From).
            So, I decided to change it to
            try ValidCharacters{Int32.From(Number.RandomBetween(0, List.Count(ValidCharacters)-0.01))} otherwise ValidCharacters{List.Count(ValidCharacters) – 1}
            Now, the probability to get”9″ is almost ok but still two times smaller for “A”.
            hmm…what to do? I changed construction to this one
            ValidCharacters{Number.IntegerDivide(try Number.RandomBetween(0, List.Count(ValidCharacters)-0.01) otherwise StrLenght -1, 1)}
            where “StrLenght” is a mistake because should be List.Count(ValidCharacters)-1.
            But there is another mistake… “try… otherwise” construction is not necessary because of Number.IntegerDivide function. So the proper “Result” step should looks like this below
            Result = Text.Combine(List.Transform( {1..StrLenght}, each ValidCharacters{Number.IntegerDivide(Number.RandomBetween(0, List.Count(ValidCharacters)-0.01), 1)}))
            Now we can receive almost the same probability to get each of characters (the initial version of the code with Number.IntegerDivide had 1 instead of 0.01, so the probability to get “9” was equal almost zero)

            I’m sorry for the confusion but I made the code by hand only ( not tested with PQ)

            Regards 🙂

          2. Author

            Thanks, Bill. I’ve updated the post with a recommendation that visitors check out your solutions in the comments. Thanks again!

Leave a Reply (Markdown formatting available)