Power Query: Too Random

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:

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:

I then convert the list to a table using the ‘To Table’ option from the Convert section on the Transform tab.

I now have a table, which means I can add columns:

I choose to add a ‘Custom Column’ from the ‘Add Column’ tab:

I decide I want values between zero [0] and 100:

The M code I have used is:

This gives me some random values:

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:

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’:

This creates another column:

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:

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:

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!