Generate Random List of Numbers in Power BI Dataset Using Power Query

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;

= List.Random(10)

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.

Summary

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.