List.RandomBetween function in Power Query Languag…

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 —

Source link

Be the first to comment

Leave a Reply

Your email address will not be published.