get random numbers with Power Query

One of the easiest ways how to generate random numbers in Power Query

If you know how to generate data in Power Query, it might be very useful to create data sets for reproducible examples. Here is one of the easiest methods how to generate random numbers in Power Query. You can combine with generated dates, time intervals, or other data that you can create with list functions.

There are functions like Number.Random or Number.RandomBetween that might be the first choice, but when used straight forward, those functions generating the same number in each row.

Below is a quick and easy approach with List.Random.

Here is a list of dates for 10 days that were transformed into the table and renamed as Date.

= List.Dates(#date(2021, 1, 1), 10, #duration(1, 0, 0, 0))

list of dates Power Query

Generate random numbers in Power Query in two or three easy steps.

1. Add a new custom column and use function List.Random to generate one random number for each row.

=List.Random(1)

2. Expand values.

expand Power Query random values

3. It might be all that you want. Multiply and round these Power Query random numbers if necessary. In the transform tab, you can find multiplication and rounding.

multiply Power Query

As a result, here are the random numbers that I generated. In my case, these are numbers between 0 and 100.

random numbers in Power Query

Another interesting example that could be done with list functions – calendar table in Power Query.





Posted

in

Comments

Leave a Reply

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