I’ve been asked from one of my friends who were trying to create a random dataset for his presentation. There is a great and quick way that you can create a random list of numbers in Power BI using Power Query. In this short blog post, I’ll explain how to create this random list. You can use the generated output for building a sample dataset to work on with Power BI Desktop. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.
List.Random: Power Query Function
There are some functions that help with creating a random value. List.Random is one of those functions, which is very helpful. You just specify the number of random numbers that you want to generate:
List.Random(<how many times>)
For example, if I want to create a list of ten random values, it would be List.Random(10). To use this function to create a sample list, it is always easier to start from a blank query.
Get Data from Blank Query
Start with creating a blank query:
If you don’t see the formula bar in Power Query Editor, this is where you can enable it: In the View tab, check the Formula Bar:
In the Formula Bar, use the expression below;
You can even use this for generating a list of 10 million numbers! However, be careful when you load all that data into Power BI! It might consume all the memory 😉
Convert to Table
You can convert this list now to a table:
When you convert a list to a table, you can split it based on a delimiter, which is not what we want here, so just click OK, on the “To Table” dialogue box. the table output now should have one column:
Changing the Range
As you see, the random number generated is between zero and one. If you want this to be on a higher value, you can always multiply it with something.
Let’s say, for example, I want this to be a random number up to 1000. I can multiply it with 1000 then.
Now, we have a table with values up to 1000;
Adding Row Number
You might also want to add a row number to the table, here is how you can do it: Under Add Column -> Index Column
And here is the dataset loaded into Power BI:
Changing the Random List on Every Function Call
By default when you use List.Random with one parameter, it will recreate the list of random numbers after each function call. It means for every step, you will see the random list changing!
If you don’t like this to happen, and you want the list to be generated randomly only once, and doesn’t change with every function call, then you can specify the seed parameter:
List.Random(<how many times to generate>,<seed>)
This won’t change with every call then:
Other Random Functions in Power Query
There are two other random functions in Power Query: Number.Random and Number.RandomBetween, they both give you a random number. However, you have to be careful when you use them on a list, because most of the times, query folding might happen, and then you get the same number for every row in the list after loading it into Power BI. I might write about tricks to use those later on.
This was a very quick blog article about how to use List.Random to generate a random dataset of numbers. Do you want to generate a random dataset and can’t get it working with Power Query? let me know down below in the comments.