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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Vijay_A_Verma

List.RandomBetween function in Power Query Language M

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.

1.png

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))

 

2.png

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

3.png

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)

 

5.png

Integer RandomNumbers

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

4.png

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

 --- End of Article ---