Power Query: Too Random
2 March 2022
Welcome to our Power Query blog. This week, I look at another way of generating random data.
Whilst producing the masterpieces that are Power Query blogs, I often need to generate random data. In Excel, I favour the RANDBETWEEN() function, but there are also functions I can use in Power Query. This week I look at the Number.RandomBetween() function, which is even more like my favourite Excel function, but with an important difference!
This M function has the following syntax:
Number.RandomBetween(bottom as number, top as number) as number
The output is a random number between bottom and top. To show how it is used, I have created a list of numbers from one [1] to 20:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I then convert the list to a table using the ‘To Table’ option from the Convert section on the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I now have a table, which means I can add columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I choose to add a ‘Custom Column’ from the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I decide I want values between zero [0] and 100:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
The M code I have used is:
= Number.RandomBetween(0,100)
This gives me some random values:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
My first question is whether Number.RandomBetween() is volatile like it’s Excel cousin RANDBETWEEN(). I can check this by refreshing, which I can do using ‘Refresh Preview’ on the Home tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
Yes, I have new values. This is important to know as it means I need some way of stabilising the output if I want to use it to generate stable data. In Excel I would Copy and ‘Paste Special’ to keep the values.
To see if something similar is possible, I select RandomBetween and right-click, I have the option to ‘Duplicate Column’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
This creates another column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
Notice that the step ‘Duplicated Column’ does not have a cog icon next to it, but this is misleading. If I refresh data again, I can see if the values change in RandomBetween – Copy:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
RandomBetween – Copy changes to match RandomBetween. Power Query executes all the steps, which means it generates the values for RandomBetween and then takes a copy. Similarly, if I add another step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/274/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
Even without refreshing, Number.RandomBetween() is generated for each step. I would need to export to Excel and take a copy from there to get a stable data set.
I will look at another random function next time.
Come back next time for more ways to use Power Query!