List.Random

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.Random

In this article, we will dive into the M code behind the List.Random function in Power Query. We will explore how this function works and how it can be used to randomly select items from a list.

What is the List.Random function?

The List.Random function is a built-in function in Power Query that is used to randomly select one or more items from a list. The function takes two arguments: the list to select from and the number of items to select.

The basic syntax for the List.Random function is as follows:

List.Random(list as list, count as number) as list

Here, `list` is the list of items to select from, and `count` is the number of items to select. The function returns a list of randomly selected items from the input list.

How does the List.Random function work?

The List.Random function works by generating a random number between 0 and the length of the input list. It then selects the item at the index corresponding to the random number, and adds it to the output list. This process is repeated for the specified number of items, until the output list contains the desired number of randomly selected items.

Here is an example of how the List.Random function works:

let

myList = {1, 2, 3, 4, 5},

selectedItems = List.Random(myList, 3)

in

selectedItems

In this example, we have a list of five items (1, 2, 3, 4, 5), and we want to randomly select three of them. The List.Random function generates three random numbers between 0 and 4 (the length of the input list), and selects the items at the corresponding indices (e.g. if the random number is 2, it selects the third item in the list). The function returns a list of the three randomly selected items.

How to use the List.Random function in Power Query

The List.Random function can be used in a variety of scenarios in Power Query. Here are a few examples:

Random sampling of data

If you have a large dataset and you want to take a random sample of the data, you can use the List.Random function to randomly select a subset of rows. For example, if you have a table with 1000 rows and you want to select 100 random rows, you can use the following code:

let

myTable = Table.FromRecords(…), // input table

rowCount = Table.RowCount(myTable),

selectedRows = List.Random(Table.ToList(myTable), 100), // randomly select 100 rows

outputTable = Table.FromRecords(selectedRows) // create output table from selected rows

in

outputTable

Random assignment of data

If you have a list of items that need to be assigned randomly to a group (e.g. participants in a study), you can use the List.Random function to randomly assign the items to groups. For example, if you have a list of 20 participants and you want to randomly assign them to two groups of 10, you can use the following code:

let

participants = {“Alice”, “Bob”, “Charlie”, …}, // input list of participants

group1 = List.Random(participants, 10), // randomly select 10 participants for group 1

group2 = List.RemoveItems(participants, group1) // remove group 1 participants from the original list to get group 2

in

{group1, group2} // return both groups as a list

Random selection of values

If you have a list of values and you want to randomly select one or more values, you can use the List.Random function to do so. For example, if you have a list of cities and you want to randomly select one for a game show, you can use the following code:

let

cities = {“New York”, “Los Angeles”, “Chicago”, …}, // input list of cities

selectedCity = List.Random(cities, 1){0} // randomly select one city and return it as a text value

in

selectedCity

The List.Random function is a useful tool in Power Query for randomly selecting items from a list. By understanding the M code behind this function, you can use it to perform a variety of data transformation tasks, such as random sampling of data, random assignment of data, and random selection of values. So next time you need to randomly select something in Power Query, remember the List.Random function and the M code behind it.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)