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!
