Generate Integer Random Number in Power Query Lang…

Use Case – Power query has 2 functions which can be used to generate random numbers – Number.Random and Number.RandomBetween. While Number.Random gives decimal outputs which are greater than 0 but less than 1, Number.RandomBetween also gives decimal outputs but number generated is between From and To numbers. See the snip below to see output of following two formulas where second formula generates random number between 100 and 9999.

 

 

= Number.Random()
= Number.RandomBetween(100,9999)

 

 

Solution – To generate integer random numbers between two numbers say 100 and 9999, you can use below formula

 

 

= Number.IntegerDivide(Number.RandomBetween(100,9999),1)

 

 

Another approach possible is that you generate random number through RandomBetween formula and then change the column type to Whole number as a second step. Only problem is that this approach requires 2 steps. 

See the output of various approaches

For Testing – See the working here – Open a blank query – Home – Advanced Editor – Remove everything from there and paste the below code to test 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sMVSK1cHBMCKCYQxjmGBoN4UxzOBSBjCWBYxhqRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Number.Random", each Number.Random(), type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Number.RandomBetween", each Number.RandomBetween(100,9999), type number),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "1.Integer Random Number", each Number.IntegerDivide(Number.RandomBetween(100,9999),1), type number),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Apply Whole Number", each Number.RandomBetween(100,9999)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"Apply Whole Number", Int64.Type}})
in
    #"Changed Type"