The M language has so-called structured values, to which lists, records and tables belong. Each value type serves specific purposes and this post is intended to give an introduction to lists. This post is part of a series about lists, records and tables in M. I have included some screenshots with M code in this post. You can download the code from the screenshots as a txt file here. The download does not require a password. If you are still asked for your OneDrive password, change your browser to Edge.

What is a list in M?

In M there are two kinds of values: primitive and structured values. Examples for primitive values are:

• „a“,
• 1,
• true.

They are primitive in that way, that they are not contructed out of other values. In contrast to primitive values, we have so called structured values in M, which are composed of other values, primitive and structured ones. A list is one of those structured values (the others are records and tables) and it is described as „an ordered sequence of values“. A defined list is introduced with an opening curly bracket „{“ and ended with a closing curly bracket „}“. Even if lists can be empty (it looks like this „{}“), lists are not limited in size, because M supports infinitely large lists.

An example of a very simple representative of a list is:

{1,2}

Before taking a more detailed look at lists of what they are and what they are for, let’s discuss why to use lists at all.

Why use lists at all?

In the vast majority of cases, a list will be used as an intermediate product for other queries. In any case, it is extremely rare for a list to be loaded into the data model.

There are many native functions in M that provide useful functionality and expect lists as one or more of their parameters. For these cases it is important to know how to create lists, so that you can provide those functions with their necessary parameters in a proper way.

In addition, there are many functions in M that return lists as return values. For these reasons it is necessary to know how to deal with them in order to use the M language safely.

Let’s take a look at how to create lists.

How to create lists in M

A list in M usually has a beginning and an end and in my experience there are 3 ways in M to create a list:

1. Using the list initialization syntax with curly braces

You can define a list by using an opening curly bracket „{„, an optional item-list and end it with a closing curly bracket „}“. The simplest list is an empty list.

Empty lists

The empty list is the simplest of all lists, in which the item-list is empty:

{} →List.Count({}) = 0

A practical use case of an empty list is, for example, creating a table in the Power BI data model that contains only measures. Unlike an empty table created in Power Query (which you can create like this: = #table({},{})), which simply doesn’t appear in the data model, an empty list is imported as a table into the data model with one column and no rows. Simply hide the resulting column in the data model and use this empty table as the measure table.

An empty list is of course more of a special case, because lists normally contain values, so-called items.

Lists with comma seperated items

To create a non-empty list, I can add comma separated values of any type to the list as follows:

If I want to add a large number of contiguous values to a list, the comma separated definition is quite complex. But there is help for that…

Lists with contiguous numbers

For example, if I want to create a list that shows the whole numbers from 1 to 100, I don’t have to manually add all these numbers to the list. Instead there is the following abbreviation:

{1..100}

If, for example, I would like the whole numbers to be listed from 1 to 100, but the number 48 should be excluded, then this works as follows:

{1..47, 49..100}

Such a sequence of contiguous values must not have more than 2^31 (2.147.483.647) values. For example, look at the following list definition:

{1..2147483648}

Due to the fact that a range of numbers greater than 2,147,483,647 is referenced here, the following error message is returned:

However, this does not mean that the list itself may not have more items, because as already mentioned, lists can be infinitely large. The following definition returns a valid result:

{1..2147483647, 1..2147483647}

The syntax „..“ for a contiguous range of integers can also be applied to characters.

Lists with contiguous characters

The procedure I have just shown for consecutive numbers is also possible for letters and characters. For example, it is valid to define the following list:

{"a".."m"}

The result then looks as follows:

It may not be surprising that Power Query „knows“ that after the „a“ comes the „b“, but look at the list {"Z".."a"} and its result:

Between the letters „Z“ and „a“ there are also a number of special characters. So what is the basis of this list and it’s sort order?

Character lists are based on Unicode

The M function Character.ToNumber() reveals the secret:

Lists of characters using the „..“-syntax internally use the Unicode definition to define both the characters and their order. So the list {"Z".."a"} is internally converted into {90..97} and finally to a list of unicode characters. If you want to see the corresponding Unicode character for a special numeric value, you can use the function Charater.FromNumber(). For example, Character.FromNumber(91) results in "[".

This ordered sequence of unicode characters is the reason, why {„a“..“Z“} produces an empty list: The list is internally converted to its numeric values, which in this case means {97..90}. Since the beginning of the defined list is before the end of the list, the result is an empty list.

Defining lists manually is one way. Let’s look at how to create lists with corresponding functions.

2. Using native M functions, that create lists

At the time of writing, we have 63 functions in M that return a list. The most obvious of them are the List.* functions (but not all of them create lists). Popular examples are:

• List.Dates(),
• List.Numbers(),
• List.Random().

Also easy to identify are the *.ToList() conversion functions (which I will discuss in more detail later), such as:

• Binary.ToList(),
• Table.ToList(),
• Text.ToList().

All those functions create lists based on the input parameters. But there is another way to create a list.

3. Referring to a column/ field in a table

The third way to create a list is to reference a column of a table using the following syntax: Table[ColumnName]:

Since this may not be obvious, I will mention here that the reference to a step name (if it returns a table) has the same effect as the reference to a table expression: #"step name"[Column].

Now that we know what lists are and how they are created, we are going to focus on dealing with lists.

Operators for lists + equivalent functions

There are 3 operators that can be used in conjunction with lists: „=“ and „<>“ make it possible to compare lists, while „&“ combines lists. Here are some examples of how to use that:

1. {1,2} = {1,2} → true
2. {1,2} <> {2,1} → true
3. {1,2} & {3,4,5} → {1,2,3,4,5}. This can also be achieved by using the function List.Combine({ {1,2}, {3,4,5} })

Another aspect that is interesting with regard to lists is how to access the items in a list.

Accessing list items

Once you have a list, it is sometimes necessary to access special items within the list directly.

Using the positional index operator {}

To access an item in a list, you can use the – so called – positional index operator „{}“ by its numeric index. The items in a list are refered to using a zero-based index. The following examples explain this in detail:

Take the following list: MyList = {1,2,3}. Because the internal index of the list items starts with zero, I get the following results:

MyList{0} = 1,

MyList{1} = 2,

MyList{2} = 3,

MyList{3} leads to the following error message, saying that an attempt is made to access an item of the list that does not exist in the list:

To overcome this last error message, you can use the so called optional-item-selection as follows, which returns null, if the selected item doesn’t exist:

MyList{3}? → null

To find out how many items are in a list, you can use the function List.Count(). List.Count(MyList) leads to result 3, so the last item in the list has index 2 (List.Count(MyList)-1), because the index is zero-based.

In addition to the examples shown above, there are also various native M functions that allow access to items in a list.

Using native M functions

The M library offers a wide range of list functions, some of which provide access to list items. For the following examples take this list as given: MyList = {1,2,3,-1, 8, 9}

List.First()

This function accesses the first item in a list, which is equivalent to using the positional index operator with index 0 (like MyList{0}).

List.FirstN()

This function works in two ways.

1. If the second parameter of the function is a number, the first list items are returned up to this number: List.FirstN(MyList, 2) = {1,2}
2. If the second parameter of the funtion is a condition, all those items are returned until the condition no longer applies for the first time: List.FirstN(MyList, each _ < 3) = {1,2}

List.Last()

This function accesses the last item in a list. The following two expressions are identical: List.Last(MyList) = MyList{List.Count(MyList)-1}

List.LastN()

Works as List.FirstN, but the other way around.

List.Range()

Returns a count items starting at an offset. List.Range(MyList, 2, 3) leads to the following result: {3,-1,8}

List.Select()

This function determines items of a list, not based on their position within the list, but based on certain conditions that this item must fulfill. The following example goes through a list and selects those items whose value is greater than 2:

List.Select(MyList, each _ > 2) which returns the following list as results: {3, 8, 9}

Functions that use lists as input

Sometimes you want to use a certain functionality in M and look for the suitable function. If, for example, I want to rename the column name of a table with dynamically vaying column names, I can do this in M with the function Table.RenameColumns(). If I ignore the last optional parameter of the function, then its structure can be described as follows:

Table.RenameColumns(Tables with column names to be changed, List with the structure {old column caption, new column caption})

The following screenshot shows an M script that renames the two existing columns of a table and is prepared for the column captions of the original table to be changed during the next run. The actual renaming of the column captions takes place in the last row of the let-expression (2). All the steps described in the red box (1) are used to create the dynamic list of old name/ new name pairs required by the Table.RenameColumns() function.

Let’s next look at functions that generate lists.

Functions that create lists as output

Many functions in M generate lists and the most obvious of them are the *.ToList functions:

*.ToList functions

Binary.ToList() – Creates a list out of binaries.

Record.ToList() – Converts a record into a list, containing the field values of the record.

Table.ToList() – Creates a list from a table by separating the columns row by row with a separator that can be defined in the optional combiner parameter (e.g., Combiner.CombineTextByDelimiter(","))

Text.ToList() – Creates a list from a text by adding each character individually as a list item.

However, the *.ToList functions are not the only functions that generate a list from a value of a certain type.

Other functions, that create lists

For some of the functions, the name does not suggest that they create lists. An example of this is the function Text.Split(). Imagine the following task: In a long text count the number of distinct words. The following script does this by using Text.Split() in combination with other list functions.

Other examples of functions, that return lists, but do not sound like that are:

• Table.Column(),
• Table.ColumnNames()
• Table.ToColumns()
• Table.ToRecords()
• Table.ToRows()

Even though there is much more to say about lists and their capabilities, I guess it has certainly become clear that lists have their charm and that it is necessary to deal with them in order to master the M language.

Greetings from Germany,

Lars