Please note javascript is required for full website functionality.


Power Query: Random Enough

9 March 2022

Welcome to our Power Query blog.  This week I look at generating random data in a list.

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.  I have looked at Number.Random() and Number.RandomBetween() which gave me volatile results.  This week, I look at List.Random().

This M function has the following syntax

List.Random(count as number, optional seed as nullable number) as list

where count is the number of values in the list to generate.  The numbers generated are between zero [0] and one [1].  I will look at the role of seed in a moment.  To show how the function is used, I start with a blank query and create a list of 20 numbers:

The M code is:

= List.Random(20)

If I ‘Refresh Preview’ from the Home tab, then the values are regenerated.

Now, I enter a seed value of three [3].

The M code is:

= List.Random(20,3)

This gives me a new list of numbers:

I use ‘Refresh Preview’ again:

My values are stable! The role of seed is to select a particular set of random numbers. If I change the seed value, I get different numbers:

These too are unaffected by refreshing. Similarly, if I add a step, the values are preserved:

I have converted the list to a table using the option on the ‘List Transform’ tab. The random values are consistent unless I change the seed. This means that I can control the random numbers, which is ideal for creating data.

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