Update 2016/06/01: Bill Szysz has shared a solution in the comments below that is shorter and easier to understand than mine. He’s also shared a couple of other alternatives that seek to improve the randomness of the results.
This post describes how to generate a random alphanumeric string in Power Query. This is likely not a common requirement for most Power Query users, but I saw this requirement in the Twitter API and thought it would be a fun challenge in M.
Here’s the complete query. Later I’ll explain how it works.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/* Generates a single, random alphanumeric string. The string length can be modified by changing the StringLength variable. Author - Chris Koester https://chris.koester.io/ */ let StringLength = 32, ValidCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456879", fnRandomCharacter = (text) => Text.Range(ValidCharacters,Int32.From(Number.RandomBetween(0, Text.Length(ValidCharacters)-1)),1), GenerateList = List.Generate(()=> [Counter=0, Character=fnRandomCharacter(ValidCharacters)], each [Counter] < StringLength, each [Counter=[Counter]+1, Character=fnRandomCharacter(ValidCharacters)], each [Character]), RandomString = List.Accumulate(GenerateList, "", (a,b) => a & b) in RandomString |
The result of this query is a value like “kZSRd55cC67QyFWbjTXKlhnwiCttaZxU”. Every time the query is run, a different value is generated.
Generate a Random Character
Here’s how the random character is generated step by step.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
let // Specifies the number of characters in the final string StringLength = 32, // Specifies the valid characters that can appear in the string ValidCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456879", // Counts number of valid characters. It is subtracted by one because the Text.Range formula below is zero-based. // This means that, according to the Text.Range formula, there are 0-61 characters instead of 1-62. If you try to // use 62 in the formula, it generates an error. NumberOfCharacters = Text.Length(ValidCharacters)-1, // Generates a random integer between 0 and 61. RandomNumber = Int32.From(Number.RandomBetween(0, NumberOfCharacters)), // The random number is used in the Text.Range formula to retrieve a random character RandomCharacter = Text.Range(ValidCharacters,RandomNumber,1) in RandomCharacter |
Generating a List
Once a random character is generated, a list containing random characters can then be generated using List.Generate. In the final version, I wrapped the individual steps above into a one-line function, so that a different character is generated each time the function is invoked. Without a function, a random character is generated once and repeated in the list, like “aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa”.
Here you can see the random character logic wrapped into a function, and List.Generate invokes the function in two places. It is invoked the first time to establish the first character in the list. Then, it is invoked repeatedly until the desired string length is reached (32 in this example).
|
1 2 3 4 5 |
fnRandomCharacter = (text) => Text.Range(ValidCharacters,Byte.From(Number.RandomBetween(0, Text.Length(ValidCharacters)-1)),1), GenerateList = List.Generate(()=> [Counter=0, Character=fnRandomCharacter(ValidCharacters)], each [Counter] < StringLength, each [Counter=[Counter]+1, Character=fnRandomCharacter(ValidCharacters)], each [Character]), |
Transform List into a Value
Once the list is generated, the list then needs to be transformed into a single value. For that, the List.Accumulate formula is used. In this example, each character in the list is simply being concatenated.
|
1 2 3 |
RandomString = List.Accumulate(GenerateList, "", (a,b) => a & b) in RandomString |
If you press the Refresh Preview button, you’ll see a new, random, alphanumeric string generated each time. If desired, this entire query could be made into a function and called from other queries. StringLength could be parameterized.
When developing this query, I had a scalar in mind. I have not tested it with generating a large number of values and I don’t know how it would perform. If you have any suggestions for improvement, please leave a comment below.
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
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.
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
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?
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 🙂
Thanks, Bill. I’ve updated the post with a recommendation that visitors check out your solutions in the comments. Thanks again!