Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Vijay_A_Verma
Super User
‎05-05-2022 11:23 PM

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"