Power Query: Totally Random

Welcome to our Power Query blog.  This week, I look at one way of generating random data.

Whilst producing the masterpieces that are these 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.Random() function.

This M function has the following syntax:

The output is a random number between zero [0] and one [1].  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 may add columns:

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

I am going to make a deliberate mistake. In the ‘Custom Column’ dialog, I name the new column Number.Random:

The M code I have used is:

Power Query detects no syntax errors, so I click OK:

A function in every column is not quite what I am looking for! If I click on one of the ‘Function’ values, I can invoke it:

This process has created two steps, ‘Number Random’ and ‘Invoked FunctionNumber Random’ (the odd spacing is down to Power Query, not me!).

The first step, ‘Number Random’, is the function page:

Therefore, the second step is calling the function.  The step ‘Invoked FunctionNumber Random’ produces the number I wanted in the column.  Notice the M code used:

The M code is:

Since I know the ‘Number Random’ step is the function, I can see that, even though the function has no parameters, I still need to enter the brackets [()].  I can go back and correct the column I added.

I go back to the ‘Added Column’ step and use the cog (gear) icon next to it to change the definition of the column.

I have changed the M code to:

This time, with the small change to the M code, I get the result I would like:

Readers who have tried Number.Random() in earlier versions of Power Query will notice that its behaviour in a column has improved. In previous versions, the same random number appeared for every row! Now I can use this column as a starting point for larger random values and dates by transforming the data.

I will look at another random function next time.

Come back next time for more ways to use Power Query!