Generating Random Numbers in Power Query

In this article, we will focus on creating random values in Power Query. We will start by looking at random numbers. Think of numbers between 0 and 1 or between specific values. After, we move on to generating sequences of random letters, dates and special characters.

If duplicates are undesired in your outcome, make sure to read the last chapter on Special Characters. Enjoy!

Table of contents

• Why Random Numbers
• Generating Random Numbers
  • Random Numbers Between 0 and 1
  • Random Numbers Between Two Values
  • Random Whole Numbers
• Duplicate Values in Custom Column
• Generate List of Random Numbers
  • List.Random Method
  • List.Generate Method
  • List.Transform Method
• Generate Random Dates
• Generate Random Letters
• Generate Random Symbols
• Conclusion

Why Random Numbers

Random numbers are numbers that are chosen by chance. They are not predictable and don’t follow a pattern. Random numbers are used in many different things, like playing games of chance or making decisions based on chance.

They are important because they help us create things that are not predictable. By generating random numbers, we can create simulations that show us what might happen if a certain event occurs. Or it can help in creating random datasets. So, how do we create random values in the Power Query M Language?

Generating Random Numbers

Power Query has three main functions that return random values. You can make use of the Number.Random and Number.RandomBetween functions to return a single value. Or to return a list of values, List.Random is your friend.

In this chapter, we’ll explore the ways to generate random numbers in Power Query, including random numbers between 0 and 1, random numbers between two values, and random whole numbers.

Random Numbers Between 0 and 1

The first function we’ll look at is the Number.Random function. This function returns a value between 0 and 1 but never returns 0 or 1. The values come with 17 decimal places. To use this function, just write Number.Random() that opens and right after closes parentheses. For example:

= Number.Random() // A random returned value: 0,15212353977939277

Random Numbers Between Two Values

If you want to generate a random number between two specific values, you can use the Number.RandomBetween function. This function requires two arguments: the lower and upper limit. For example, if you want to generate a number between 0 and 1, you would write:

= Number.RandomBetween( 0, 1 ) // Random returned value: 0,88214660942654433

Since this behaviour is similar to Number.Random, it’s more likely you will want to return values between bigger numbers. You can, for example, write:

= Number.RandomBetween( -500, 0 ) // Random returned value: -34,60102506661832

The below code will return the error.

Random Whole Numbers

Sometimes, you need a random list of whole numbers instead of decimal values. To do this, you can use a combination of different functions. Some options include:

= Number.IntegerDivide(   Number.RandomBetween(1, 100), 1 )
= Number.Round(           Number.RandomBetween(1, 100), 0 )
= Number.RoundTowardZero( Number.RandomBetween(1, 100) )
= Int64.From(             Number.RandomBetween(1, 100) )

Duplicate Values in Custom Column

Now, with these examples, you’re equipped to add your own custom column in Power Query. You easily add them using the Add Column feature, and all looks good.

You load your query into Power BI’s data model, and all of a sudden, the data looks different. You now have a random number repeated in the entire column. Yet when you look in the Power Query preview screen, you see different values. How can that be?

This has to do with an optimization Power BI does in the background. It’s very unintuitive and a source of frustration. The easiest way to prevent this from happening is to do the following.

1. In your query, create the desired Random Number.
2. As a next step, create an Index Column.
3. Then, delete that column again.
4. Load in your data and the duplicate random value should be gone.

It’s not pretty, but it works. Let’s hope Microsoft fixes this at some point!

Okay. So far, this has been great, but the methods only return a single value. What can you do if you want to return a list of random numbers?

Generate List of Random Numbers

You may want to make a list of random numbers. This can come in handy when you want to create a random dataset. This section delves into three methods of how to create a random sequence of numbers.

List.Random Method

The easiest way to make a list of random numbers is by using the List.Random function. It has two arguments. First, you tell it how many numbers to generate. And second, you can give it a seed value.

The seed value is like a secret code that tells the function to make the same list of random numbers each time you use it. If you don’t give it a seed number, the function generates a new list of random numbers every time you use it.

= List.Random( 3 ) 
// Returns a list of 3 random values. For example:
// { 0.094352828848340009, 0.80067050680549379, 0.085040570742003885 )

The numbers will be between 0 and 1, just like Number.Random function. If you want numbers up to 1000, you can multiply each number by 1000 using List.Transform.

= List.Transform( List.Random( 3 ), each _ * 1000 ) 
// Returns 3 values between 0 and 1000

This code returns 3 random values. Each time you press Refresh Preview in Power Query, the values will change. Below is an example of how refreshing your preview 3 times changes values.

In case you want the numbers to be random, but stay the same during refresh, you can provide the seed value.

= List.Random( 3 )     // These 3 random values change on refresh
= List.Random( 3, 1 )  // These 3 random values stay the same on refresh
= List.Random( 3, 10 ) // These 3 random values stay the same on refresh

Alternatively, to get more flexibility, you can also use the List.Generate to create a list of random numbers.

List.Generate Method

You can also create a list of random numbers is by using List.Generate. You can provide how many numbers you want and then tell the function what to do.

For example, to create a list of Random Numbers, you can make use of both List.Generate and Number.Random.

= List.Generate(
  () => [ Random = Number.Random(), Count = 1 ], 
  each [Count] <= 150, 
  each [ Random = Number.Random(), Count = [Count] + 1 ], 
  each [Random]
)

This creates 150 numbers between 0 and 1. Learn more about List.Generate in this article.

So far you could have achieved the same with the List.Random function. But what if you want to create a list of Random Numbers Numbers Between Two custom values?

Not a problem. You can call Number.RandomBetween within List.Generate by writing:

= List.Generate(
  () => [ Random = Number.RandomBetween(1, 100), Count = 1 ], 
  each [Count] <= 150, 
  each [ Random = Number.RandomBetween(1, 100), Count = [Count] + 1 ], 
  each [Random]
)

This will give you a list of 150 random numbers between 1 and 100. For those looking for an even easier method that’s very flexible too, get ready for the third method using List.Transform.

List.Transform Method

The third method to create a list of random numbers uses List.Transform. First, you create a list of numbers that functions as a placeholder. To create a list of 150 values, we can write:

= { 1 .. 150 }

These numbers are unrelated to the Random Numbers we want. Now, to get 150 random Numbers between 1 and 100, you replace each of these values as follows:

= List.Transform( {1 .. 150}, 
                  each Number.RandomBetween( 1, 100 ) )

With that same trick, you can create random 9-digit numbers. You would generate 9 unique values between 0 and 10. Then, after creating the list, you can combine them using the Text.Combine function.

= Text.Combine( 
     List.Transform( {1 .. 9},
          each Text.From( Int64.From( Number.RandomBetween( 0, 9 )))))
// Random value returned "73981105107"

Generate Random Dates

To generate a sequence of random dates, you can make use of a similar technique. The steps to do this are:

1. Find the number equivalent of the lower and upper limit dates you want to generate dates for.
2. Generate numbers between those values.
3. Transform the values into dates.

So, let’s say you want to generate random dates between June and August 2023. The limits for this date range are:

= let StartDate = Number.From( #date( 2023, 6, 1) ), 
      EndDate = Number.From( #date( 2023, 8, 31) ) 
  in 
     Date.From( Number.RandomBetween(StartDate, EndDate ) )

What’s left is to decide how many values you want to return. The easiest all-in-one solution you have is by using List.Transform. To generate 10 random dates in Power Query, we can write:

= List.Transform( {1..10}, 
    each 
      let
        StartDate = Number.From(#date(2023, 6, 1)), 
        EndDate   = Number.From(#date(2023, 8, 31))
      in
        Date.From(Number.RandomBetween(StartDate, EndDate)) 
  )

Generate Random Letters

Power Query only has functions available that create random numbers. So, what can we do if we want to create a sequence of random letters?

You can make use of the Unicode values each character represents. For example,

• the letters “A” to “Z” in Unicode are values 65 up to 90.
• the letters “a” to “z” are represented by the values 97 up to 122.
• Numbers 1 to 9 are represented by 48 to 57

The trick is to generate numbers between these values and transform them back to the character they represent by using Character.FromNumber.

You can create a sequence of random letters by writing:

= List.Transform( { 1..10 }, each 
  let Start = Character.ToNumber( "a" ), 
      End   = Character.ToNumber( "z" )
  in
    Character.FromNumber( Int64.From( Number.RandomBetween( Start, End) ) ) )

Similarly, you can generate capital letters by writing:

= List.Transform( { 1..10 }, each 
  let Start = Character.ToNumber( "A" ), 
      End   = Character.ToNumber( "Z" )
  in
    Character.FromNumber( Int64.From( Number.RandomBetween( Start, End) ) ) )

It’s good to keep in mind that the above method may return duplicates. After all, the Number.RandomBetween function returns decimal numbers and the Int.From function only uses the integer part. It’s very likely you bump into a duplicate at some point.

If duplicates are a problem, have a look at the next section. The method below works without duplicates.

Generate Random Symbols

What’s left is to generate a sequence of random symbols in Power Query. What’s different about this approach is that it returns a sequence of unique random symbols, whereas earlier methods could return duplicates.

Generating a list of random symbols can be a little trickier. The Unicode Standard contains 1.481 characters belonging to the Latin script. And there is no consecutive list of Unicode characters that represent symbols.

For the sake of an example, let’s say we want to create a random series of values belonging to one of the below Unicode values.

• 32 – 47
• 58 – 64
• 91 – 96
• 123 – 126

You can provide the entire list of characters manually, but that’s tedious. To generate a list of values with all of the above Unicode combinations, you can write:

= { 32 .. 47, 58 .. 64, 91 .. 96, 123.. 126 }

You can learn more in this Complete Guide on Lists in Power Query. Now, to retrieve a sequence of random special characters in Power Query, we will perform the following steps:

1. Specify how many random values we want to return
2. Sort the character list in a random order, using Number.Random as sort criteria.
3. Retrieve FirstN values desired.
4. Restore Unicode characters to special characters

Let’s say we want to retrieve 5 random special characters from this list. We first grab the initial list and sort it using custom criteria. If you need a refresher, this article is a deep dive into the List.Sort function and explains how to use the custom sort criteria.

SortedDate = 
   List.Sort( { 32..47, 58..64, 91..96, 123..126 }, 
                each Number.Random() ) 

The result is a randomly sorted list. And each time you refresh your preview, the sort order will change.

Next, we retrieve the desired number of values. In our example, we will retrieve 5 random special characters.

FirstNCharacters = 
    List.FirstN( SortedData, 5 )

So far, the code returns only the Numbers representing the Special characters. What’s left is to restore the special characters and give the output.

= List.Transform( FirstNCharacters , 
    each Character.FromNumber(  _ ) )
// Returns 5 random special characters from the list

The final code you can use to create a random list of special characters is:

= let 
      UnicodeCharacters = { 32..47, 58..64, 91..96, 123..126 },
      ReturnNItems = 5,
      SortedData =   List.Sort( UnicodeCharacters, each Number.Random() ),
      TopNValues =   List.FirstN( SortedData, ReturnNItems ),
      RandomValues = List.Transform( FirstNCharacters, 
                                     each Character.FromNumber( _ ) )
  in  
      RandomValues

Conclusion

In conclusion, Power Query provides various functions and methods to create random values, such as random numbers, dates, letters, and symbols. By using the different methods provided, you can easily create random datasets and simulations.

It’s important to keep in mind that some of these methods can return duplicate values. To ensure unique values, check out the method shown in Chapter 7. Whether you’re creating a simulation or a random dataset, Power Query has you covered with its versatile random value generation capabilities.