Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Vijay_A_Verma

Generate Integer Random Number in Power Query Language M

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)

 

 

Random.png

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

Random2.png

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"