Lists in Power Query M / List Functions (200+ Examples)

Lists in Power Query are often used for data transformations. You commonly find lists of numbers, letters, times, dates etc. And these lists are useful by themselves, yet combining them with other functions really makes them stand out.

Lists allow you to take on advanced data challenges where you apply logic on lists. Yet you can start using them at any stage in your journey. To become an expert in the Power Query M-language mastering lists is essential.

This post is the complete guide that introduces you to list. It shows you the characteristics of a list, how to use them and access values. More importantly, you will find lots of practical examples that you can adjust to your needs. This is a long post, so buckle your seat belts!

1. What are Lists?

A list is a series of values. They are required to make use of the most powerful Power Query functions. You can visualize a list as a single column of data, whereas a table is a combination of many columns of data.

To create a list manually you write comma separated values and surround them by curly brackets { }. The curly brackets indicate the start and end of a list. The following list contains three values, and 8 is the second value.

= {4, 8, 12}

Just like a Record and Table a List is a so called structured data value. The list can contain any M value, including a list itself. It is possible to have an empty list, a list with text or numbers, but you can also mix different data types together.

= {}                    // Returns an empty list
= {1, 2, 3}             // A sequence of consecutive numbers
= {"A", "B", "C"}       // A list of text values
= {"Rick", "De,Groot" } // Text values in a list can have comma's
= {1, "ABC", true}      // List with a number, text and logical 

Power Query lists can also contain more advanced data types. It is perfectly valid to mix numbers, letters, records, table objects and list objects within a single list. Meaning that an item in a list can also be a list, creating a list with nested lists.

= {{1, 2},{8, 9}}        // Returns a list containing two lists

// Returns a list containing two records
=  { [Name = "Bob", Age = 29], [Name = "Ava", Age = 44] }

2. Generate Lists

The Power Query M-language supports different ways to create lists. This chapter shows you how to leverage constructs and functions to create lists from scratch.

2.1. Consecutive Numbers

There is an easy way to generate a series of consecutive numbers. You can create a list by using curly brackets and then input a starting value and ending value, while separating the values by the two dots operator (..). This generates a list between two numbers.

= {1 .. 6}         // Equals {1, 2, 3, 4, 5, 6}  
= {1..3, 7..9}     // Equals {1, 2, 3, 7, 8, 9}                
= {-2 .. 3}        // Equals {-2, -1, 0, 1, 2, 3}

The before examples create a list of numbers but only for increasing sequences. Trying this with a decreasing sequence results in an empty list.

To generate a decreasing sequence you can use several other functions. For example, one way to generate a list of incremental numbers is by using the List.Numbers function. This function returns a list of numbers starting from a value and by default adds increments of 1. You can adjust this increment to get a list of decimal numbers, or and even negative numbers.

= List.Numbers( 1, 5 )      // Equals {1, 2, 3, 4, 5} 
= List.Numbers( 1, 5, 1 )   // Equals {1, 2, 3, 4, 5} 
= List.Numbers( 1, 5, -1 )  // Equals {1, 0, -1, -2, -3, -4}
= List.Numbers( 1, 5, 0.1 ) // Equals {1, 1.1, 1.2, 1.3, 1.4, 1.5}

Other ways to generate a decreasing sequence are by using the List.Reverse and List.Generate functions. The List.Reverse function takes an increasing series and reverses it. Whereas the List.Generate function applies a function for an indicated amount of steps.

= {6 .. 1}                   // Equals {}, does not work

= List.Reverse( {1 .. 6} )   // Equals {6, 5, 4, 3, 2, 1}

// Equals {6, 5, 4, 3, 2, 1}
= List.Generate( () => 6, each _ >= 1, each _ - 1 )

Power Query can generate any consecutive list as long as it fits within a 32 bit integer value. This means you can only create a continuous list that is smaller than 232 or 2^32. The result of this is 2.147.483.648.

= {1.. 2147483647}  // starting from 1 this is the longest list
                     // of Numbers Power Query can create

Any longer continuous list results in the error: “Expression.Error: The number is out of range of a 32 bit integer value.”

2.2. Consecutive Letters

In a similar way, you can create a sequence of continuous letters. The only difference is that you surround each text value with quotations.

= {"a", "b", "c", "d"}    // List from a to d
= {"a" .. "d"}            // Similar list as above

= {"A", "B", "C", "D"}    // List with Capitals A to D
= {"A" .. "D"}            // Similar list as above

2.3. Consecutive Dates

There are several functions you can use to generate a list of dates. The most obvious function to use is the List.Dates function. The List.Dates function generates a list that begins with a starting date and increments with a specified value.

= List.Dates(                  // Generates list of 5 dates
     #date(2021, 10, 15),      // Starting from Oct 15th 2021
     5,                        // Adding 5 increments
     #duration(1, 0, 0, 0)     // Of a single day
 )

Alternatively you can generate a list of numbers that represent your date range. You can then turn those numbers into a continuous list of dates by transforming the data type. The following examples use the parameters StartDate and EndDate to create a list of dates between two dates:

= { Number.From( StartDate ) .. Number.From( EndDate ) }

= List.Numbers(
     Number.From( StartDate ),
     Duration.Days( EndDate - StartDate   ) + 1
  )

= List.Generate( 
     () => Number.From( StartDate ),
     each _ <= Number.From( EndDate),
     each _ + 1
  )

After you enter the before code, you can change the data type to date. This results in a list of consecutive days. You can find a more elaborate explanation on the previous examples in this article on creating the ultimate date table.

2.4. Special Characters

A special way to create a list is by using characters. Power Query allows you to create a consecutive list of special characters using a similar syntax as before. The bigger question is, how do they decide the right order?

In reality, Power Query treats all characters the same. Under the hood it uses unicode. To make it easy for you as user, you can simply put in the character you wish.

= {"!", """", "#", "$", "%", "&"}     // Special characters
= {"!" .. "&"}                        // Similar list

If instead you want to reference Unicode values, you can make use of the Character.FromNumber function. This function translates the Unicode to the corresponding special character. Since most people do not know these by heart, the regular characters do make your life easier. If you want to retrieve the corresponding unicode value of a character you can find it by using Character.ToNumber.

// Generates the characters ! to &
= { Character.FromNumber(33) .. Character.FromNumber(38) }

// Generates the numbers 1 to 10
= { Character.FromNumber(48) .. Character.FromNumber(57) }

// Generates the letters A to Z
= { Character.FromNumber(65) .. Character.FromNumber(90) }

// Generates the letters a to z
= { Character.FromNumber(97) .. Character.FromNumber(122) }

2.5. Custom Series

There are several functions to generate lists with custom series. For example, if you want to show numbers that increase with a step of 2 you can try use List.Alternate. The List.Alternate function takes the original list as input, needs a count to specify how many steps to skip. Optionally you can add an interval and offset. The interval shows the amount of values to return before skipping values and the offset tells how many values to pass before skipping values.

= List.Alternate(
      list, 
      count,           // number of values to skip each time  
      repeatInterval,  // num values added between skipped values
      offset           // offset before skipping values
) 

= List.Alternate( {1..10}, 0 )   // Equals {1..10} skip no value
= List.Alternate( {1..10}, 3 )   // Equals {4..10} skip 3 values

= List.Alternate( {1..10}, 0, 1)  // Equals {1..10}
= List.Alternate( {1..10}, 1, 1)  // Equals {2, 4, 6, 8, 10}
= List.Alternate( {1..12}, 2, 1)  // Equals {3, 6, 9, 12}
= List.Alternate( {1..8},  1, 2)  // Equals {2, 3, 5, 6, 8}
= List.Alternate( {1..12}, 3, 3)  // Equals {4, 5, 6, 10, 11, 12}
                                  // skip 3 values before step
                                  // add 3 values each step

= List.Alternate( {1..10}, 0, 1, 0) // Equals {1..10}
= List.Alternate( {1..10}, 1, 1, 0) // Equals {2, 4, 6, 8, 10}
= List.Alternate( {1..10}, 1, 1, 1) // Equals {1, 3, 5, 7, 9}
                                    // skip 1 value before step
                                    // add 1 value each step
                                    // skip values at offset 1

A more advanced way of generating custom lists is by using the List.Generate function. You can build a series using logic. As long as the given condition is true, the serie values increment.

= List.Generate(
     initial value,          // the starting value
     condition as function,  // as long as the condition is true
     next as function,       // increment the value
     optional selector
  )

// Equals {1, 2, 3, 4, 5, 6} 
= List.Generate( () => 1, each _ <= 6, each _ + 1 )

// Equals {2, 4, 6, 8, 10, 12}
= List.Generate( () => 2, each _ <= 12, each _ + 2 )

// Equals {1, 3, 5, 7, 9, 11}
= List.Generate( () => 1, each _ <= 12, each _ + 2 )

When you want to repeat a list of values you can make use of the List.Repeat function. It takes a list as input and duplicates it for a given amount of times.

= List.Repeat( {1, 2, 3}, 3 )    // {1, 2, 3, 1, 2, 3, 1, 2, 3}
= List.Repeat( {"a", "b"}, 3 ) // {"a", "b", "a", "b", "a", "b"}

To create a list of random numbers, Power Query has the function List.Random. The List.Random function requires one argument with the number of random values to generate. It has an optional second argument which is the seed value. If you enter the seed value with a number, each call to the function results in the same list of random numbers.

= List.Random( 3 )     // {0.982217873, 0.744085241, 0.281192752}
                       // On refresh above list changes

= List.Random( 3 ,2 )  // {0.771093898, 0.404162595, 0.165998677}
                       // On refresh above list remains the same

When you want to split your list into a list of lists you can use the List.Split function. The List.Split function takes a list as input. Then in the second argument you can indicate the size of each of the lists. When splitting into lists of two it looks similar to the List.Zip function, yet the order of the values is different.

= List.Split( {1..8}, 2) // Equals {{1,2}, {3,4}, {5, 6}, {7, 8}}
= List.Split( {1..8}, 3) // Equals {{1, 2, 3}, {4, 5, 6}, {7, 8}}
= List.Split( {1..8}, 4) // Equals {{1, 2, 3, 4}, {5, 6, 7, 8}}

= List.Split( {"a".."f"}, 2) // Equals {{"a", "b"}, {"c", "d"}, {"e" }} 

There are several functions that require you to generate a list containing one or more lists of pairs. These lists of pairs contain the old value and the new value to replace. For example, you find this in Table.TransformColumnTypes, Table.TransformColumns, TransformColumnNames and Table.RenameColumns

It would be arduous to write these combinations by hand. Instead Power Query knows List.Zip. The List.Zip function allows you to combine different lists, returning a list of lists. The values in each list have in common that each has the same position in the original list. So the first list contains all first items from all lists, the second lists contains all second values from the given lists, etc.

// Returns {{"a", "A"}, {"b", "B"}, {"c", "C"}}
= List.Zip( {{"a", "b", "c"}, {"A", "B", "C"}} )

// Returns {{"a", "A", 1}, {"b", "B", 2}, {"c", "C", 3}}
= List.Zip( {{"a", "b", "c"}, {"A", "B", "C"}, {1, 2, 3}} )

// In actual scenarios you will often reference column names
= List.Zip( {TableName[Column1], TableName[Column2] } )

This section showed different ways to generate lists from scratch. Yet there are other functions that result in a list.

3. Other Ways to Retrieve Lists

Besides creating lists from logic, there are other ways to create a list.

3.1. Reference Columns

This method explains how to create a list from column values. You can see a table as multiple lists next to each other. Knowing this, it may come natural that referencing a column results in a list. If you want to work with the values in a column you can extract column values by writing: TableName[ColumnName]. This syntax turns the column values into a list.

The same happens when you right-click a column header and press drill-down.

3.2. Non-List Functions

This section shows non-list functions that result in a list. For as this article focuses on lists and list functions, I will but shortly describe the functions.

  • Table.Column: Returns the column values from the table as a list.
  • Table.ColumnNames: Generates a list of column names in the table as list of text.
  • Table.Split: splits a table into a list of tables.
  • Table.SplitAt: returns a list containing two tables.
  • Table.ToColumns: creates a list of nested lists from the table.
  • Table.ToList: converts a table to a list of records.
  • Table.ToRecords: converts a table to a list of records.
  • Table.ToRows: creates a list of nested lists from the table
  • Text.ToList: Creates a list of individual characters from the text value.
  • Text.Split: Transforms text into a list of text values based on a single specified delimiter.
  • Text.SplitAny: Returns a list of text values based on the splitting by any specified delimiter.
  • Binary.ToList: Converts a binary value into a list of numbers.
  • Record.ToList: Returns a list of values containing the field values from the input.
  • Lines.FromBinary: Converts a binary value to a list of text values split at lines breaks. 
  • Lines.FromText: Converts a text value to a list of text values split at lines breaks

Because the mentioned functions result in a list, you can apply list functions to them. This allows you to retrieve information, transform the lists, or filter the lists. Now you know different ways to create lists it is time to learn how to access values within the list.

4. Access Values from Lists

You can assess values from a list by applying a zero-based index inside curly brackets. In a zero-based index, the first value in a list has position 0. Earlier you learned that braces { } indicate the start and end of a list. Yet when you add a zero-based index number between curly braces behind the list, it retrieves the relating value from the list. It gives you item access to the list values by its location.

= {1, "ABC", true}{1}        // Returns the value "ABC"

= {{ 1, 2 },{8, 9}}{0}       // Returns the first list {1, 2}

// Returns the second item from the first list
= {{ 1, 2},{8, 9}}{0}{1}

// Returns the first record
= {[ Name = "Bob", Age = 29 ], [ Name = "Ava", Age = 44 ]}{0}

// Returns the Name column from the first record in a list
= {[ Name = "Bob", Age = 29 ], [ Name = "Ava", Age = 44 ]}{0}[Name]

//Returns {2, 1} the column values in the row where A=2 
= #table( {"A","B"},{{0,1},{2,1}} ){[A=2]} 

Yet if the zero-based index number is bigger than the amount of values in your list, Power Query throws the error: “Expression.Error: There weren’t enough elements in the enumeration to complete the operation.” It means that the location does not exist in the existing list.

You can prevent this error from happening by adding a question mark behind the expression. This action returns null if the list does not contain enough values. For example:

= {1, "ABC", true}{4}     // Returns an error
= {1, "ABC", true}{4}?    // Returns null

5. Working with Lists

5.1. Retrieve Values

An easy way to retrieve the first or last element in a list is by filtering it using the List.First and List.Last functions. You can provide an optional default value for when the list is empty.

= List.First( {1, "ABC", true} )     // Returns 1
= List.First( {} )                   // Returns null
= List.First( {}, "Missing" )        // Returns default "Missing"

= List.Last(  {1, "ABC", true} )     // Returns true
= List.Last( {} )                    // Returns null
= List.Last( {}, "Missing" )         // Returns default "Missing"

// Alternatively you can find the amount of items in a list .
// and subtract one, to retrieve a zero-based index.
= {1, "ABC", true} {List.Count( {1, "ABC", true} ) - 1}

// Which is the same as
= {1, "ABC", true}{2}

With this logic you can replicate the COALESCE function in Power Query. It returns the first value that is not null, or the default value if everything returns null.

// Returns 1
= List.First( List.RemoveNulls( {null, 1, 2} ) ) 

// To return a default value you can use one of the following: 
= List.First( List.RemoveNulls( {null, null, "Default"} ) ) 
= List.First( List.RemoveNulls( {null, null} ), "Default" ) 

If instead you want to retrieve a range of values at the start or end of a list you can use List.FirstN or List.LastN. Both functions have 2 arguments. The list to search, yet the second argument can hold either a count or a condition. As second argument:

  • when you enter a number, up to that many items are returned
  • when you enter a condition, the function return all items that initially meet the condition. So once a value fails the condition, no other items are considered.
= List.FirstN( {1, 2, 3, 4},     2)         // Returns {1, 2}
= List.FirstN( {1, "ABC", true}, 2)         // Returns {1, "ABC"}

= List.FirstN( {3, 2, 4, 1}, each _ <= 3)               // {3, 2}
= List.FirstN( {3, 5, 4, 1}, each Number.IsOdd(_))      // {3, 5}

= List.LastN(  {1, 2, 3, 4},     2)         // {3, 4}
= List.LastN(  {1, "ABC", true}, 2)         // {"ABC", true}

= List.LastN( {3, 2, 4, 1 }, each _ <= 3)              // {1}
= List.LastN( {3, 5, 4, 12}, each Number.IsEven(_) )   // {4, 12}

The functions List.MinN and List.MaxN allow you to retrieve minimum or maximum values from a list.

= List.MinN( {1, 1, 2, 3, 4, 5, 6 }, 2)     // Equals {1, 1}
= List.MinN( {1, 10, 3, 14, 6 }, 3)         // Equals {1, 3, 6}

= List.MaxN( {1, 1, 2, 3, 4, 5, 6 }, 3)     // Equals {6, 5, 4}
= List.MaxN( {1, 10, 3, 14, 6 }, 3)         // Equals {14, 10, 6}

You can also extract a range of values in the middle of your Power Query list by using List.Range. The List.Range function requires you to enter the number of values to retrieve and indicate from which offset position it should retrieve the values.

= List.Range( {1, 2, 3, 4},    0, 2)       // Returns {1, 2}
= List.Range( {1, 2, 3, 4},    2, 2)       // Returns {3, 4}
= List.Range( {1, 1, 2, 3, 4}, 2, 3)       // Returns {2, 3, 4}

A more flexible way to filter a list is by using List.Select. The List.Select function allows you to create a function which determines what values to keep. In other words, it returns all values that match a condition.

With such conditions, you are able to only show a certain data type. For example, only keep numbers, texts or lists from your list.

// Returns values smaller or equal to two: {1, 1, 2}
= List.Select( {1, 1, 2, 3, 4, 5, 6}, each _ <= 2 )

// Returns negative values: {-1, -2}
= List.Select( {-1, -2, 5, 6}, each _ < 0 ) 

//Returns even numbers: {2, 4, 6}
= List.Select( {1, 1, 2, 3, 4, 5, 6}, each Number.IsEven(_) )

// Returns odd numbers: {1, 1, 3, 5}
= List.Select( {1, 1, 2, 3, 4, 5, 6}, each Number.IsOdd(_) )

// Returns text values only: {"a", "c"}
= List.Select( {"a", 1, "c", 3, 4, 5, 6}, 
               each Value.Type(_) = type text ) 

// Returns number values only: {1, 4, 5, 6}
= List.Select( {"a", 1, "c", 3, 4, 5, 6}, 
               each Value.Type(_) = type number ) 

// Returns list values only: {{3,4,5}}
= List.Select( {"a", 1, "c", {3, 4, 5}, 6}, 
               each Value.Type(_) = type list ) 

5.2. Remove Values

You can also remove a range of values from a list by using List.RemoveFirstN and List.RemoveLastN. Both functions have 2 arguments. The first argument is the list to search, and the second argument can hold either a count or a condition. As second argument:

  • when you enter a number, up to that many items are removed.
  • when you enter a condition, the function removes all items that initially meet the condition. So once a value fails the condition, no other items are considered.
= List.RemoveFirstN( {1, 2, 3, 4}, 2 )                  // {3, 4}
= List.RemoveFirstN( {1, 2, 3, 4}, 3 )                  // {4}
= List.RemoveFirstN( {3, 2, 4, 1 }, each _ <= 3)        // {4, 1}

= List.RemoveLastN( {1, 2, 3, 4}, 2 )                   // {3, 4}
= List.RemoveLastN( {1, 2, 3, 4}, 3 )                   // {1}
= List.RemoveLastN( {5, 4, 12}, each Number.IsEven(_) ) // {5}

When you want to remove a list of values from a list you can use List.RemoveItems or List.RemoveMatchingItems. At first sight these two functions look identical. They both remove items from list1 that are present in list2. However, the List.RemoveMatchingItems function has an optional equation criteria. It for example allows you to control equality testing and with that ignore capital letters.

// Returns {1, 2, 3, 6}
= List.RemoveItems( {1, 2, 3, 4, 5, 6}, {4, 5} )

// Returns {1, 2, 3, 6}
= List.RemoveMatchingItems( {1, 2, 3, 4, 5, 6}, {4, 5} )

// Returns {"a", "b", "c", "d"}, searches capital sensitive
= List.RemoveMatchingItems( {"a", "b", "c", "d"} ,{"A", "B"} )

// Returns {"c", "d"}, searches capital insensitive
= List.RemoveMatchingItems( {"a", "b", "c", "d"} ,{"A", "B"}, 
     Comparer.OrdinalIgnoreCase )

Before example showed how to remove matching items. Sometimes you only want to keep matching items. You can use List.Intersect to keep the matching items between lists. The function has an optional equation criteria that allows you to ignore capitals. The function also works with multiple lists and supports duplicate values.

= List.Intersect( {{1, 2, 3}, {2}} )             // Equals {2} 
= List.Intersect( {{1..4}, {2..5}, {3..6}} )     // Equals {3, 4}
= List.Intersect( {{1, 1, 3}, {1}} )             // Equals {1}
= List.Intersect( {{1, 1, 3}, {1, 1}} )          // Equals {1, 1}

// Equals {"a", "b"} 
= List.Intersect( {{"a", "b", "c", "d"} ,{"a", "b"}} )   

// Equals {}, respects capitalization
= List.Intersect( {{"a", "b", "c", "d"} ,{"A", "B"}} )   

// Equals {"a", "b"}, ignores capitalization
= List.Intersect( {{"a", "b", "c", "d"} ,{"A", "B"}}, 
     Comparer.OrdinalIgnoreCase )  

Alternatively you can keep all values in a list that do not appear in another list by using the List.Difference function. This function also has an optional equation criteria value. It support duplicate values and only accepts two lists.

= List.Difference( {1, 2, 3}, {2} )              // Equals {1, 3} 
= List.Difference( {1..4}, {2..3} )              // Equals {1, 4}
= List.Difference( {1, 1, 2}, {2, 2, 2} )        // Equals {1, 1} 
 

// Equals {"c", "d"} 
= List.Difference( {"a", "b", "c", "d"} ,{"a", "b"} )   

// Equals {"a", "b", "c", "d"} respects capitalization
= List.Difference( {"a", "b", "c", "d"} ,{"A", "B"} )   

// Equals {"c", "d"} ignores capitalization
= List.Difference( {"a", "b", "c", "d"} ,{"A", "B"}, 
     Comparer.OrdinalIgnoreCase )  

In case you want to remove null values from a list, use List.RemoveNulls.

// Returns {1, 2, 0, 1, 2} by removing null but keeping 0's
= List.RemoveNulls( {1, null, 2, null, 0, 1, 2} )

You can remove a continuous range of values with the List.RemoveRange function. You can input an offset and a number of values to remove. The default number of values removed is 1.

= List.RemoveRange( {1, 2, 4}, 2)            // Returns {1, 2}
= List.RemoveRange( {1, 2, 4}, 2, 1)         // Returns {1, 2}

= List.RemoveRange( {1, 2, 4, 5}, 2, 2)      // Returns {1, 2}
= List.RemoveRange( {1, 2, 4, 5}, 3, 1)      // Returns {1, 2, 4}

At other times you may want to skip a few values in your list but retrieve all others. You can easily do that by using List.Skip. The first argument is the list to search, and the second argument can hold either a count or a condition. As second argument:

  • when you enter a number, up to that many items are skipped
  • when you enter a condition, the function skips all items that initially meet the condition. So once a value fails the condition, no other items are considered.
= List.Skip( {1, 2, 3, 4}, 1)                // Returns {2, 3, 4}
= List.Skip( {1, 2, 3, 4}, 2)                // Returns {3, 4}

= List.Skip( {1, 2, 3, 4},    each _ <= 3)   // Returns {4}
= List.Skip( {9, 5, 1, 2, 6}, each _ >= 5)   // Returns {1 ,2, 6}

One of the most used list functions is List.Distinct. This function returns a list that contains all values from the source list but removes duplicates.

= List.Distinct( {1, 1, 2, 1, 2, 5, 6} ) // Returns { 1, 2, 5, 6}
= List.Distinct( {"a", "a", "b", "b"} )  // Returns { "a", "b"}

 // Returns {{2, 2}, {1, 1}}
= List.Distinct( {{2, 2}, {1, 1}, {1, 1}} )

5.3. Add Values

Previous section showed you how to remove list values or how to keep list values. This section shows how you can add new data to your lists. When we look at functions, you can make use of List.Union, List.Combine or List.InsertRange.

  • List.Combine concatenates two lists into a single list.
  • List.Union combines a list of lists, transforms them into a single list and keeps only unique values. You can add the equationCriteria to control equality testing. Comparer.OrdinalIgnoreCase allows you to ignore capitals.
  • List.InsertRange lets you insert a list of values at a specified position in the input list.
= List.Combine( {{0, 1, 2}, {2, 3}} )   // Equals {0, 1, 2, 2, 3}

// Returns unique list of values
= List.Union( {{0, 1, 2}, {2, 3}} )     // Equals {0, 1, 2, 3}

// Returns unique list, respecting capitals {"a", "b", "B", "c"}
= List.Union( {{"a", "b"}, {"B", "c"}} ) 

// Return unique list, ignore capitals {"a", "b", "c"}
= List.Union( {{"a", "b"}, {"B", "c"}} , 
              Comparer.OrdinalIgnoreCase)

// Equals {0, -1, -2, 1, 2}
= List.InsertRange( {0, 1, 2}, 1, {-1, -2} ) 

5.4. Transform Values

The next area we look at is transforming the values in a list. A function that transforms values in a list is List.Transform. The List.Transform function takes each item in a list and transforms it using a function. The functions you can create are only limited by your creativity. See the following examples:

= List.Transform( initial list, function to transform list )

= List.Transform( {1, 2, 3}, each _ + 1)      // Equals {2, 3, 4}
= List.Transform( {1, 2, 3}, each _ * 2)      // Equals {2, 4, 6}

// Returns {"Amy", "May", "Joe"}
= List.Transform( {"amy", "may", "joe"}, Text.Proper) 

// Returns first two characters of each value {"am", "ma", "jo"}
= List.Transform( {"amy", "may", "joe"}, each Text.Start( _, 2) )

A more advanced function to transform values is List.TransformMany. The List.TransformMany function takes an initial list (x), takes an intermediate step to transform this list (y), and then uses a function to combine the results (x and y).

= List.TransformMany(
     initial list,         // initial list x
     (x)   => ...,         // transforms elements of list x to y
     (x,y) => ...          // combines results of x and y

//Returns {"1 - Jan", "2 - Feb", "3 - Mar", "4 - Apr","5 - May"}
= List.TransformMany(
     {1..5},                    
     (x)   => {#date(2021, x ,1)},   
     (x,y) => Text.From(x) &" - "& Date.ToText( y, "MMM" ))) 

// Returns {"First day 2021: Friday", "First day 2022: Saturday", 
//          "First day 2023: Sunday"}
= List.TransformMany( 
     {2021..2023},
     (x)     => {#date(x, 1, 1)},
     (x, y ) => "First day " & Text.From(x) & ": " & 
                Date.DayOfWeekName(y)
  )

5.5. Replace Values

Another way to change values is by replacing them. You can replace values in a list with the function List.ReplaceValue. The function searches within a list of values and replaces matching values with a replacement. Just like the Table.ReplaceValue function, the List.ReplaceValue function has a replacer function. You can either replace when a value has a partial match or when it has an exact match. To understand the differences you can read more on this in my article on replacing values.

// Replaces with partial match {"B1r", "CAT", "Te1"}
= List.ReplaceValue( {"Bar", "CAT", "Tea"}, "a", "1", 
                     Replacer.ReplaceText )

// Replaces with exact match {"Bar", "CAT", "Tea"}
= List.ReplaceValue( {"Bar", "CAT", "Tea"}, "a", "1", 
                     Replacer.ReplaceValue )

// Replaces with exact match {"1", "CAT", "Tea"}
= List.ReplaceValue( {"Bar", "CAT", "Tea"}, "Bar", "1", 
                     Replacer.ReplaceValue )

Imagine doing 5 replace operations. You would have 5 separate steps in your query. You can consolidate these with another function. The List.ReplaceMatchingItems function can replace multiple sets of values in a list. The function has three arguments.

  1. the original list
  2. one or more lists of two values with the old value and the new value, provided in a list
  3. Optional equation criteria (for example Comparer.OrdinalIgnoreCase)
// One list with replacement values, returns {10, 2, 3, 4, 5}
= List.ReplaceMatchingItems( {1, 2, 3, 4, 5}, {{1, 10}})

// Two lists with replacement values, return {10, 2, 30, 4, 5}
= List.ReplaceMatchingItems( {1, 2, 3, 4, 5}, {{1, 10}, {3, 30}} )

// Three lists with replacement values, return {10, 2, 30, 4, 50}
= List.ReplaceMatchingItems( {1, 2, 3, 4, 5}, 
                             {{1, 10}, {3, 30}, {5, 50}} )

// One list with replacement values, returns {"z", "b", "c", "d"}
= List.ReplaceMatchingItems( {"a", "b", "c", "d"}, {{"a", "z"}} )

// Capital sensitive replace returns {"a", "b", "c", "d"}
= List.ReplaceMatchingItems( {"a", "b", "c", "d"}, {{"A", "z"}} )

// Ignores capitals and returns {"z", "b", "c", "d"}
= List.ReplaceMatchingItems( {"a", "b", "c", "d"}, {{"A", "z"}}, 
                             Comparer.OrdinalIgnoreCase )

In some cases you may want to replace a range of values, regardless of their contents. The function to replace a range of values is List.ReplaceRange. List.ReplaceRange replaces a given amount of values with a replacement list starting at a specified position. This allows you to for example replace 3 values from the original list with 5 values of your replacement list.

// Returns {1, 2, 3, 4, 5}
= List.ReplaceRange( {1, 1, 1, 4, 5}, 1, 2, {2, 3} )

// Returns {"Avi", "Ben", "Dan", "Ace"}
= List.ReplaceRange( {"A", "B", "Dan", "Ace"}, 0, 2, {"Avi", "Ben"} )

// Notice how you can replace 4 values and return only 2. 
// Or the other way around.

// Returns {"Avi", "Ben"}
= List.ReplaceRange( 
     {"A", "B", "Dan", "Ace"}, 0, 4, {"Avi", "Ben"} )

// Returns {"Aad", "Bas", "Cas", "Gia", "Dan", "Ace"}
= List.ReplaceRange( 
     {"A", "B", "Dan", "Ace"}, 0, 2, {"Aad", "Bas", "Cas", "Gia"} )

5.6. Reorder Values

Many times, the order in which your values show is important. If those cases you can sort your data with the List.Sort function. The List.Sort function is a very flexible function that allows you to transform data before you sort it.

It takes a list as first argument and has an optional comparisonCriteria as second argument. When you leave the second argument empty, it automatically sorts in an Ascending way.

You can control the sort order with the comparison criteria. That can be one with simple functions like Order.Ascending or Order.Descending.

= List.Sort( {2, 3, 1} )                     // Returns {1, 2, 3}
= List.Sort( {2, 3, 1}, Order.Ascending )    // Returns {1, 2, 3}
= List.Sort( {2, 3, 1}, Order.Descending)    // Returns {3, 2, 1}

= List.Sort( {"A", "B", "Da", "Ac"} ) // Returns {"A", "Ac", "B", "Da"}

// Returns {"Da", "B", "Ac", "A"}
= List.Sort( {"A", "B", "Da", "Ac"}, Order.Descending) 

You can also transform the list of values. For example by making the numbers absolute using Number.Abs, and then optionally include a sort order. Or by transforming letters to uppercase using Text.Upper so you can sort capital insensitive.

// For sorting you can transform numbers to absolute values.

// Returns {1, 5, -8, -9}
= List.Sort( {1, 5, -8, -9}, each Number.Abs( _ ) )

 // Returns similar to previous: {1, 5, -8, -9}, 
= List.Sort( {1, 5, -8, -9}, {each Number.Abs( _ ), Order.Ascending} )
// Returns previous in reverse order: {-9, -8, 5, 1}
= List.Sort( {1, 5, -8, -9}, {each Number.Abs( _ ), Order.Descending} )


// You can transforms letters uppercase to sort case insensitive

= List.Sort( {"a", "A", "b", "B"} ) // Returns {"a", "b", "A",  "B"}

// Returns similar to previous: {"a", "A", "b", "B"}
= List.Sort( {"a", "A", "b", "B"}, each Text.Upper(_) )

// Sorts previous list in Descending order: {"b", "B", "a", "A"}
= List.Sort( {"a", "A", "b", "B"}, 
             {each Text.Upper(_), Order.Descending} )

Yet you can even apply logic through a function to indicate a custom sort order. This is helpful when you can’t use any text or logical functions to indicate the correct order. You often see helper columns for this, but it can be achieved without.

// Returns {"Feb, "Feb", "Jan", "Jan", "Mar"
= List.Sort( {"Feb", "Jan", "Feb", "Jan", "Mar"} )

// Returns {"Jan, "Jan", "Feb", "Feb", "Mar"}
= List.Sort( {"Feb", "Jan", "Feb", "Jan", "Mar"}, 
             ( x,y ) => Value.Compare(
                        List.PositionOf( {"Jan","Feb","Mar"}, x),
                        List.PositionOf( {"Jan","Feb","Mar"}, y)
                      )
  )

Perhaps the order of your list is not alphabetical or numerical. Yet you want to reverse the order of your data. For those instances, you can make use of the List.Reverse function in Power Query.

= List.Reverse( {2, 3, 1} )            // Returns {1, 3, 2}
= List.Reverse( {"A", "B", "G", "F"} ) // Returns {"F", "G", B", "A"}

6. Find Values in Lists

6.1. Find Position of Values

If you want to know where a value occurs in a list, you can make use of the functions List.PositionOf and List.PositionOfAny.

  • List.PositionOf: searches for a value and returns the position of the first match in the list.
  • List.PositionOfAny: searches for a list of values and returns the position of the first match in the list.

The List.PositionOf function is helpful when you want to know if a value occurs in a list. The function searches for a value and returns the index number of the first value that matches in the list. Its arguments are: 1. list of values, 2. value to search for, 3. optional number of occurrences of the value, 4. optional equation criteria. When the value does not appear in the list, the function returns -1.

= List.PositionOf( {1, 3, 5, 7, 9}, 9)              // Returns 4
= List.PositionOf( {"ape", "duck", "cow"}, "cow")   // Returns 2

// Returns {0, 2}, the third parameter indicates how many 
// values to return when multiple values match in a list
= List.PositionOf( {"ape",  "duck", "ape", "cow"}, "ape", 2)

// Returns -1 because "Ape" does not exist with a capital
= List.PositionOf( {"ape", "duck", "cow"}, "Ape") 

// Returns 0 because search for "Ape" ignores case
= List.PositionOf( 
     {"ape", "duck", "cow"}, 
     "Ape", 
     1, 
     Comparer.OrdinalIgnoreCase ) 

// Returns {0, 2}, number of occurrences is 2, capitals ignored
= List.PositionOf( 
     {"ape",  "duck", "ape", "cow"}, // List to search
     "Ape",                          // Value to match
     2 ,                             // Max Num of Values
     Comparer.OrdinalIgnoreCase )    // Ignore Capitals

The List.PostionOfAny function allows you to search for multiple values in your list. The function uses a search list with values and compares it with the original list. It then returns the offset position of the first value from the original list that matches a value from the search list.

Just like with the earlier function, you can provide the number of occurrences to return in argument 3, and the equation criteria in argument 4.

= List.PositionOfAny( {1, 2, 3, 4}, {2, 3} )    // Returns 1
= List.PositionOfAny( {1, 2, 3, 4}, {2, 3}, 2)  // Returns {1, 2}

// Returns {}, it has no match because search is case sensitive
= List.PositionOfAny( 
     {"ape",  "duck", "ape", "cow"}, 
     {"APE", "DucK"},                
     2  )

// Returns {0, 1, 2} because search ignores case.
= List.PositionOfAny( 
     {"ape",  "duck", "ape", "cow"}, // List to search
     {"APE", "DucK"},                // Values to match
     2 ,                             // Max Num of Values
     Comparer.OrdinalIgnoreCase )    // Ignore Capitals

6.2. Test if List Contains Values

Another common requirement is to check whether a list contains a value. There are three versions of this function: List.Contains, List.ContainsAll and List.ContainsAny. By default all of these look for an exact match. From these three functions, the last two can check if a list contains either one or all of multiple values.

  • List.Contains: returns true when a list contains the searched value
  • List.ContainsAll: returns true when a list contains all values from a list of values
  • List.ContainsAny: returns true when a list contains any value from a list of values

Let’s see some examples. The List.Contains function contains an optional equation criteria. Comparer.OrdinalIgnoreCase indicates whether it should respect capitalization in its search. Yet you can also apply your custom function.

// The following examples look for an exact match
= List.Contains( {2, 4, 6, 8}, 6)                // Returns true
= List.Contains( {2, 4, 6, 8}, 1)                // Returns false
= List.Contains( {2, 4, 6, 8}, "6")              // Returns false
= List.Contains( {"a", "b", "c"}, "a" )          // Returns true
= List.Contains( {"a", "b", "c"}, "A" )          // Returns false
= List.Contains(                                 // Returns true
     {"a", "b", "c"}, "A", Comparer.OrdinalIgnoreCase )

// For a partial match you can apply a custom equation criteria.
// Returns true
= List.Contains( {"Rick", "RickdeGroot", "GorillaBI"}, "ick",
                 (x, y) => Text.Contains(x, y) )

// Returns true, example is capital insensitive
= List.Contains( {"Rick", "RickdeGroot", "GorillaBI"}, 
            "ICK",
            (x, y)=> Text.Contains(Text.Lower(x), Text.Lower(y))
   )

By applying the before logic, you can use the List.Contains function to replicate the IN function in Power Query.

Besides checking for a single value, you can also check whether a list contains all values within a list by using the List.ContainsAll function.

= List.ContainsAll({2, 4, 6, 8}, {3, 4})         // Returns false
= List.ContainsAll({2, 4, 6, 8}, {2, 4})         // Returns true
= List.ContainsAll({"a", "b", "c"}, {"a", "b"})  // Returns true
= List.ContainsAll({"a", "b", "c"}, {"A", "b"})  // Returns false
= List.ContainsAll(                              // Returns true
     {"a", "b", "c"}, {"A", "b"}, Comparer.OrdinalIgnoreCase ) 

Lastly, you can also check whether a list contains one value from a list of values using the List.ContainsAny function.

= List.ContainsAny({2, 4, 6, 8}, {3, 5})         // Returns false
= List.ContainsAny({2, 4, 6, 8}, {3, 4})         // Returns true
= List.ContainsAny({"a", "b", "c"}, {"a", "b"})  // Returns true
= List.ContainsAny({"a", "b", "c"}, {"A", "B"})  // Returns false
= List.ContainsAny(                              // Returns true
     {"a", "b", "c"}, {"A", "B"}, Comparer.OrdinalIgnoreCase ) 

Any of the earlier functions can easily be reversed to check if a list does not contain a value. You can use the not operator for that.

= not List.Contains( {2, 4, 6, 8}, 6)            // Returns false
= not List.ContainsAll({2, 4, 6, 8}, {3, 4})     // Returns true
= not List.ContainsAny({2, 4, 6, 8}, {3, 5})     // Returns true

This section showed how to test whether a list contains one or more values. These functions require the exact list of items to check for. But what if you want to test if the values in your list satisfy certain conditions?

6.3. Test if Values Satisfy Condition

If you want to test if the values in a list satisfy a condition you can make use of the List.MatchesAny and List.MatchesAll functions.

  • List.MatchesAny: returns true when any of the list values satisfies the condition function.
  • List.MatchesAll: returns true when all of the list values satisfies the condition function.
//List.MatchesAny tests if any value satisfies the condition
= List.MatchesAny( {2, 4, 6}, each _  < 10) // Returns true
= List.MatchesAny( {2, 4, 6}, each _  > 10) // Returns false
= List.MatchesAny(                          // Returns true
       {1, 2, 3}, each Number.IsEven(_) ) 

//List.MatchesAny tests if all value satisfies the condition
= List.MatchesAll( {2, 4, 6}, each _  < 10) // Returns true
= List.MatchesAll( {2, 4, 6}, each _  > 10) // Returns false
= List.MatchesAll(                          // Returns false
       {1, 2, 3}, each Number.IsEven(_) ) 

6.4 Return Values Containing Text

Your list may contain all kinds of text values. If you want to select only the text values that contain specified characters you can use the List.FindText function. The List.FindText function takes a list and returns the values that contain the specified text value. The condition needs to satisfy both the characters and the case.

// Returns {"bird", "fish"}
= List.FindText( {"ape",  "duck", "bird", "fish"}, "i")

// Returns {}, the function searches case sensitive 
= List.FindText( {"ape",  "duck", "bird", "fish"}, "I")

// A solution is to turn list values into uppercase
// You can then search the transformed list. 
= List.FindText( 
       List.Transform( {"ape",  "duck", "bird", "fish"}, 
                       each Text.Upper(_) 
       ), "I"
  )

If you want a similar result and ignore capitalization, you can also use a combination of List.Select and Text.Contains:

// The following syntax has a similar result: {"bird", "fish"}
= List.Select( {"ape",  "duck", "bird", "fish"}, 
       each Text.Contains( _, "i") )

// Below adjustment ignores capitals: {"bird", "fish"}
= List.Select( {"ape",  "duck", "bird", "fish"}, 
       each Text.Contains( _, "I", Comparer.OrdinalIgnoreCase ) )
 

7. List Operators

There are three operators that work with lists in Power Query.

7.1. Combine or Concatenate Lists

A way to combine lists in Power Query is by using the ampersand (&) also known as the concatenate operator. You can put the ampersand between the lists you want to combine.

//  Combines 2 lists of number values
= {3, 4} & {1, 2}          // {3, 4, 1, 2}

//  Combines 2 lists of text values
= {"a", "b"} & {"C", "D"}  // {"a", "b", "C, "D"} 

// Combines list of numbers and text values
= {"a", "b"} & {1, 2}      // {"a", "b", 1, 2}

You can also concatenate list values using the List.Combine function. It is good to remind yourself that the List.Combine function requires you to wrap the lists you want to combine within curly brackets. That means below construct has three sets of curly brackets for combining two lists.

= List.Combine( {{"a","b"}, {1, 2}} ) // Equals {"a", "b", 1, 2}

Another way to combine lists is by using List.Union. The difference between List.Union and List.Combine is in the values it returns. The List.Union function returns a list of unique values, whereas the List.Combine function includes duplicates.

= List.Combine( { {1,2}, {1, 2} } )        // Equals {1, 2, 1, 2}
= List.Union(   { {1,2}, {1, 2} } )        // Equals {1, 2}

7.2. Compare Lists

You may have times where you want to compare two lists. To check if two lists are equal you can use the equal (=) or unequal (<>) operators. You will find that two lists with values in a different order are not equal.

= {2, 4} = {2, 4}              // returns true, same order
= {2, 4} = {4, 2}              // returns false, different order
= {2, 4} <> {4, 2}             // returns true, different order

8. Perform Math Operations on Lists

Power Query offers a selection of math functions that work with lists. Many of them perform standard math operations. You can get an idea of the list length by counting the number of values in a list. But you can also think of calculating the average, summing values, finding the minimum and maximum and many more. The most used ones you can find below:

= List.Count(   {1, 1, 2, 3, 4, 5, 6} )       // Equals 7
= List.Sum(     {1, 1, 2, 3, 4, 5, 6} )       // Equals 22
= List.Mode(    {1, 1, 2, 3, 4, 5, 6} )       // Equals 1
= List.Modes(   {1, 1, 2, 3, 4, 5, 6} )       // Equals 1
= List.Average( {1, 1, 2, 3, 4, 5, 6} )       // Equals 3.142857
= List.Median(  {1, 1, 2, 3, 4, 5, 6} )       // Equals 3
= List.Max(     {1, 1, 2, 3, 4, 5, 6} )       // Equals 6
= List.MaxN(    {1, 1, 2, 3, 4, 5, 6}, 3)     // Equals {4, 5, 6}
= List.Min(     {1, 1, 2, 3, 4, 5, 6} )       // Equals 1
= List.MinN(    {1, 1, 2, 3, 4, 5, 6}, 2)     // Equals {1, 1, 2}
= List.Product( {1, 1, 2, 3, 4, 5, 6} )       // Equals 720
= List.StandardDeviation( {1, 1, 2, 3, 4, 5, 6} ) // Equals 1,951

9. Other List Functions

You have learned about many different list functions. And you may find that there are many other list functions than the ones we have seen so far. And knowing a range of them can be handy when you want to apply transformations on the contents of a column. Other common list functions this article does not cover are:

  • Date, DateTime, Time and Duration functions
  • Buffer function: List.Buffer
  • Information functions like List.IsEmpty, List.IsDistinct, List.NonNullCount, List.Positions

To learn more about these and other functions, see the Microsoft List Functions Overview.

That was a long one! Did you learn anything new? And are there list functions you want to see more content on? Let me know in the comments.

Enjoy Power Query!

About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

Leave a comment