List.RandomBetween function in Power Query Language M

[Vijay_A_Verma]

Use Case - Power Query has Number.Random and Number.RandomBetween. It also has List.Random but it doesn't have List.RandomBetween function. 

Solution - Below formula can be used to generate a list of numbers between two given numbers. In below formula, I am generating 5 random numbers between 1 and 99.

= List.Generate(()=>[i=0], each [i]<5, each [i=[i]+1], each Number.RandomBetween(1,99))

 This will generate 5 decimal random numbers between 1 and 99.

In case, you need only integer random numbers not decimal decimal numbers, then use following formula for List.RandomBetween

= List.Generate(()=>[i=0], each [i]<5, each [i=[i]+1], each Number.IntegerDivide(Number.RandomBetween(1,99),1))

Seed in List.Random - List.Random has a seed parameter. Purpose of seed is to generate fixed random numbers every time.

Hence, if you use List.Random(5) and List.Random(5,2) where 2 is a seed number, then you get following output where List.Random(5,2) will always be the same whenever you generate

The above formulas would not work when you need a seed parameter. Following will be formulas for List.RandomBetween when seed parameter is required

Decimal RandomNumbers

= List.Transform(List.Random(5,2),(x)=>x*(99-1)+1)

Integer RandomNumbers

= List.Transform(List.Random(5,2),(x)=>Number.IntegerDivide(x*(99-1)+1,1))

The pbix file containing above formulas can be downloaded from below.

 --- End of Article ---

List_RandomBetween.pbix