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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *