Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

List Functions in Power Query Thumbnail

Unlock the power of data transformations with List Functions in Power Query. From basic to advanced challenges, the versatile list functions take your skills to the next level. Understand lists, apply logic, and access values with ease and start mastering the M-language today.

Whether you’re a beginner or an expert, this guide offers practical examples and tips to help you succeed. Buckle up for an in-depth journey!

Table of contents

What is a List?

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 with 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 the 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 

So far the values in the list were primitive values. Yet 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 list containing 2 lists
 
=  { [ Name = "Bob", Age = 29 ],        // Returns list containing 2 records
     [ Name = "Ava", Age = 44 ] }
 
= #table( {"a"}, { { 1 }, { 2 } } )     // Creates table of 1 column 2 rows
 
= { #table( {"a"}, { { 1 }, { 2 } } ),  // Creates a list
    #table( {"a"}, { { 1 }, { 2 } } ) } // Containing 2 table objects

Generate Lists

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

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 lists 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 list 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 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 }

Another way to generate a decreasing sequence is 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.

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

// The following statements return { 5, 4, 3, 2, 1 }
= List.Reverse( { 1 .. 5 } )
= List.Numbers( 5, 5, -1 )
= List.Generate( ( ) => 5, 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.”

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

Consecutive Dates

You can use several list functions 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(   #date( 2021, 10, 15 ),    // Start list at this date
                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.

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 a 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.

= { Character.FromNumber( 33 ) .. Character.FromNumber( 38 ) }  //  Output: ! to &

= { Character.FromNumber( 48 ) .. Character.FromNumber( 57 ) }  // Output: 1 to 10

= { Character.FromNumber( 65 ) .. Character.FromNumber( 90 ) }  // Output: A to Z

= { Character.FromNumber( 97 ) .. Character.FromNumber( 122 ) } // Output: a to z

Custom Series

You can find different list 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, and 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 series 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
  )

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

The List.Generate function is one of the most powerful ones but is tricky to master. To get a better understanding you can read my in-depth article on the List.Generate function in Power Query or work out how to use it with API-calls.

In a similar fashion, you can use List.Accumulate to generate a series of values.

= List.Accumulate(            // List.Accumulate,
     list as list,            // by using the items in this list,
     seed as any,             // transforms the seed (starting) value
     accumulator as function  // with this function
) as any
 
= List.Accumulate(
  {0 .. 12},                  // the list used as input
  "",                         // the starting value
  ( state, current ) =>
    if state = "" then { current }
       else if Number.IsEven( current ) then state & { current }
               else state) // Returns even numbers { 0, 2, 4, 6, 8, 10, 12 }
 
= List.Accumulate(
  {0 .. 12},                  // the list used as input
  "",                         // the starting value
  ( state, current ) =>
    if state = "" then { current }
       else if Number.IsOdd( current ) then state & { current }
               else state) // Returns odd numbers { 1, 3, 5, 7, 9, 11 }

In this example, we generated a list of values, but you can also use List.Accumulate to return a scalar value, table, record or a combination of these. Further your reading with this article full of examples with List.Accumulate or create a fast running total with it.

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) // { { 1,2 }, { 3,4 }, { 5, 6 }, { 7, 8 } }
= List.Split( { 1..8 }, 3) // { { 1, 2, 3 }, { 4, 5, 6 }, { 7, 8 } }
= List.Split( { 1..8 }, 4) // { { 1, 2, 3, 4 }, { 5, 6, 7, 8 } }

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

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 the list functions Table.TransformColumnTypes, Table.TransformColumns, TransformColumnNames and Table.RenameColumns. You can use these to rename columns in bulk.

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 list contains all second values from the given lists, etc.

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

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

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

//Or you can achieve the same, but shorter with:
= Table.ToRows( TableName[[Column1], [Column2] ] )

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

Other Ways to Retrieve Lists

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

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 naturally 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.

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 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 a text list.
  • 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 line breaks. 
  • Lines.FromText: Converts a text value to a list of text values split at line 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.

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}

= { { 1, 2},{ 8, 9 } }{0}{1}   // Returns 2nd item from the 1st list

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

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

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

Yet if the zero-based index number is bigger than the number 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

Working with Lists

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}

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 the second argument:

  • when you enter a number, up to that many items are returned
  • when you enter a condition, the function returns 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)                     // { 1, 2 }
= List.FirstN( { 1, "ABC", true }, 2)                     // { 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 }

You can use the List.FirstN function to compute a running total.

The functions List.MinN and List.MaxN allows 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.

= List.Select( { 1, 1, 2, 3, 4, 5, 6 },             // Values <= to 2
               each _ <= 2 )                        // { 1, 1, 2 }
 
= List.Select( { -1, -2, 5, 6 },                    // Negative values only
               each _ < 0 )                         // { -1, -2 }

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

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

= List.Select( { "a", 1, "c", 3, 4, 5, 6 },         // Text values only
               each _ is text )                     // { "a", "c" }

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

= List.Select( { "a", 1, "c", 4, 5, 6 },            // Number values only
                each _ is number )                  // { 1, 4, 5, 6 }

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

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

With what you just learned you can replicate the COALESCE operator in Power Query. It returns the first value that is not null, or the default value if everything returns null.

// The following statement return 1
= List.First( List.RemoveNulls( { null, null, 1, 2 } ) ) 
= List.First( List.RemoveFirstN( { null, null, 1, 2 }, each _ = null ) )
= List.RemoveFirstN( { null, null, 1, 2 }, each _ = null ){0}
= List.Select( { null, null, 1, 2 }, each _ <> null ){0}
= null ?? 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" ) 

Remove Values

You can also remove a range of values from a list by using List.RemoveFirstN and List.RemoveLastN. Both list 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 the 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 )                     // { 1, 2 }
= 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.

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

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

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

= List.RemoveMatchingItems( { "a", "b", "c" }, // Returns { "c" }
                            { "A", "B" },      // capital insensitive search
                            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 }
 
= List.Intersect( { { "a", "b", "c", "d" },     // Equals { "a", "b" } 
                  { "a", "b" } } )  

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

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

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 supports 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 } 
 
= List.Difference( { "a", "b", "c", "d" },    // Equals { "c", "d" } 
                   { "a", "b" } )                    

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

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

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

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 the 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 } )    // { 1, 2, 5, 6 }
= List.Distinct( { "a", "a", "b", "b" } )     // { "a", "b" }
= List.Distinct( { {2, 2}, {1, 1}, {1, 1} } ) // { {2, 2}, {1, 1} }

Add Values

The 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 list 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.
=  { 0, 1, 2 } & { 2, 3 }                        // Combines lists { 0, 1, 2, 2, 3 }
= List.Combine( { { 0, 1, 2 }, { 2, 3 } } )      // Combines lists { 0, 1, 2, 2, 3 }
= List.Union( { { 0, 1, 2 }, { 2, 3 } } )        // Combine unique values { 0, 1, 2, 3 }
= List.Union( { { "a", "b" }, { "B", "c" } } )   // Respects case { "a", "b", "B", "c" }
= List.Union( { { "a", "b" }, { "B", "c" } } ,   // Returns unique list,
              Comparer.OrdinalIgnoreCase )       // Ignores capitals { "a", "b", "c" }
= List.InsertRange( { 0, 1, 2 }, 1, { -1, -2 } ) // Returns { 0, -1, -2, 1, 2 }

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 performs a transformation for each item in the list.

This transformation is based on 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 }
= List.Transform( { "amy", "may", "joe" }, Text.Proper ) // { "Amy", "May", "Joe" }

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

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
)
 
List.TransformMany(
     { 1..5 },                   
     ( x )    => { #date( 2021, x, 1 ) },  
     ( x, y ) => Text.From( x ) & " - " & Date.ToText( y, "MMM" ) )
)
//Returns { "1 - Jan", "2 - Feb", "3 - Mar", "4 - Apr","5 - May" }
 
List.TransformMany(
     { 2021..2023 },
     ( x )     => { #date( x, 1, 1 ) },
     ( x, y )  => "First day " & Text.From( x ) & ": " &
                Date.DayOfWeekName( y )
  )
// Returns { "First day 2021: Friday", "First day 2022: Saturday", 
//           "First day 2023: Sunday" }

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.

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

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

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

Imagine doing 5 replacement 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)
= List.ReplaceMatchingItems( { 1, 2, 3, 4 }, // Returns { 10, 2, 3, 4 }
                             { { 1, 10 } } ) 
 
= List.ReplaceMatchingItems( { 1, 2, 3, 4 }, // Returns { 10, 2, 30, 4 }
                             { { 1, 10 }, { 3, 30 } } ) 
 
= List.ReplaceMatchingItems( { 1, 2, 3, 4 }, // Returns { 10, 2, 30, 400 }
                             { { 1, 10 }, { 3, 30 }, { 4, 400 } } ) 
 
= List.ReplaceMatchingItems( { "a", "b", "c" }, // Returns { "z", "b", "c" }
                             { { "a", "z" } } )
 
= List.ReplaceMatchingItems( { "a", "b", "c" }, // Returns { "a", "b", "c"}
                             { { "A", "Z" } } ) // Capital sensitive
 
= List.ReplaceMatchingItems( { "a", "b", "c"},  // Returns { "z", "b", "c" }
                             { { "A", "z" } },
                             Comparer.OrdinalIgnoreCase ) // Ignore capitals 

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.

= List.ReplaceRange( { 1, 1, 1, 4, 5 }, // List values to replace
                     1,                 // Starting Index for replacement
                     2,                 // Number of Values to replace
                     { 2, 3 } )         // Replace with these values
// Returns { 1, 2, 3, 4, 5 }

= List.ReplaceRange( { "A", "B", "Dan", "Ace" }, // List values to replace
                     0,                 // Starting Index for replacement
                     2,                 // Number of Values to replace
                     { "Avi", "Ben" } ) // Replace with these values
// Returns { "Avi", "Ben", "Dan", "Ace" }

--
// Notice how you can replace 4 values and return only 2. 
// Or the other way around.
--
List.ReplaceRange( 
  { "A", "B", "Dan", "Ace" }, 
  0,
  4,                  // Number of Values to replace
  { "Avi", "Ben" } )  // Replace with these values
// Returns { "Avi", "Ben" }

List.ReplaceRange( 
  { "A", "B", "Dan", "Ace" },
  0,
  2,                  // Number of Values to replace
  { "Aad", "Bas", "Cas", "Gia" } ) // Replace with
 // Returns { "Aad", "Bas", "Cas", "Gia", "Dan", "Ace" }

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 the first argument and has an optional comparisonCriteria as the second argument. When you leave the second argument empty, it automatically sorts in an Ascending way. There are so many options, this article dives into all List.Sort’s secrets.

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" } )      // { "A", "Ac", "B", "Da" }
 
= List.Sort( { "A", "B", "Da", "Ac" }, Order.Descending)
// Returns { "Da", "B", "Ac", "A" }

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.
  = List.Sort( { 1, 5, -8, -9 },     // Returns { 1, 5, -8, -9 }
             each Number.Abs( _ ) )
  
= List.Sort( { 1, 5, -8, -9 },       // Returns { 1, 5, -8, -9 }
             { each Number.Abs( _ ), Order.Ascending } )
 
= List.Sort( { 1, 5, -8, -9 },       // Returns { -9, -8, 5, 1 }
             { 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" }
   
= List.Sort( { "a", "A", "b", "B" },  // Returns { "a", "A", "b",  "B" }
             each Text.Upper( _ ) )
   
= List.Sort( { "a", "A", "b", "B" },  // Returns  { "b", "B", "a", "A" }
             { 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 it.

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

The regular sorting order is wrong for month names. You can fix that by applying the following code:

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

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" }

Find Values in Lists

Find Position of Values

If you want to know where a value occurs in a list, you can make use of the list 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 occurrence parameter
4. optional equation criteria.

By default the function returns the position of the first occurrence of the substring.

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

However, you can use the occurrence parameter in the optional third argument to change this behavior. You can use:

Occurrence.First – returns the position of the first occurrence of the searched value
Occurrence.Last – returns the position of the last occurrence of the searched value
Occurrence.All – returns a list of positions of all occurrences of the searched value

When the searched value does not appear in the list, the function returns -1.

// Use the Occurrence parameter to input how many values to return
// Below example returns: { 0, 2 }
= List.PositionOf( { "ape",  "duck", "ape", "cow" },
                   "ape",
                   2 )
// The Occurrence parameter also determines which value to return
= List.PositionOf( { "ape", "duck", "ape", "cow" },
                   "ape",
                   0 )                               // Returns 0
= List.PositionOf( { "ape", "duck", "ape", "cow" },    
                   "ape",
                   Occurrence.First )                // Returns 0
= List.PositionOf( { "ape", "duck", "ape", "cow" },    
                   "ape", 
                   Occurrence.Last)                  // Returns 2
= List.PositionOf( { "ape", "duck", "ape", "cow" },    
                   "ape",
                   Occurrence.All )                  // { 0, 2 }
= List.PositionOf( { "ape", "duck", "cow" },         // Returns -1
                   "Ape" )                           // "Ape" not in list
= List.PositionOf( { "ape", "duck", "cow" },
                   "Ape",
                   1,                                // Returns 0
                   Comparer.OrdinalIgnoreCase )      // Ignore case
= List.PositionOf( { "ape",  "duck", "ape", "cow" }, // Returns { 0, 2 }
                   "Ape",                           
                   2 ,                              
                   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 List.PositionOf function, you can provide the occurrence parameter 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 }

// The occurrence parameter impacts which value to return
= List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 }, Occurrence.First) // 1
= List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 }, Occurrence.Last ) // 2
= List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 }, 
                      Occurrence.All )  // { 1, 2 }

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

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

Test if List Contains Values

Another common requirement is to check whether a list contains a value. There are list functions that are helpful here: 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 the 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( { "a", "b", "c" }, "A", Comparer.OrdinalIgnoreCase ) // Returns true

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

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

= List.Contains(      // Returns true, shows an alternative way
     { "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 operator 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( { "a", "b", "c" },                 // Returns true
                    { "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( { "a", "b", "c" },                 // Returns true
                    { "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?

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( { 1, 2, 3 }, each Number.IsEven( _ ) )   // Returns true
 
//List.MatchesAll 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( { 1, 2, 3 }, each Number.IsEven( _ ) )   // Returns false

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.

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

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

 // A solution is to transform the list values into uppercase
= List.FindText(             // Returns {"BIRD", "FISH"}
       List.Transform( { "ape",  "duck", "bird", "fish" },
                       each Text.Upper( _ ) ),
       "I" ) 
= List.FindText( { "ape", "duck", "bird", "fish" },"i" ) // Returns { "bird", "fish" }
= List.FindText( { "ape", "duck", "bird", "fish" },"I" ) // Returns {}, case sensitive 
 
 // A solution is to transform the list values into uppercase
= List.FindText(             // Returns {"BIRD", "FISH"}
       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 ) )

List Operators

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

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.

= { 3, 4 } & { 1, 2 }          // Combines number lists  { 3, 4, 1, 2 }
= { "a", "b" } & { "C", "D" }  // Combines text lists { "a", "b", "C", "D" } 
= { "a", "b" } & { 1, 2 }      // Combines number and text lists { "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 the 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 }

Compare Lists

You may have times when 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

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 list functions 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}, 3)         // 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,9518

Even if these may seem basic operations, some proof is extremely useful as I show in my article on summing null values.

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. Knowing a range of them can be handy when you want to apply transformations to 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 Powerquery.how List Functions Overview.

Conclusion

Well, there you have it folks! I’ve covered virtually all list functions in Power Query and provided you with practical examples to illustrate the possibilities each function has. From List.Generate to List.Sum, you should now have a solid understanding of how to use these functions to transform your data more effectively.

By mastering these functions, you’ll be able to easily perform calculations on your lists and make your queries more robust.

I hope you found this article helpful and that you’re excited to start using these list functions in your Power Query projects. Remember, practice makes perfect, so don’t be afraid to experiment with the examples and come up with your own. If you have any questions or need further clarification on any of the topics covered in this article, please feel free to comment below.

Thanks for reading, and happy querying!

Share on:
  1. Hello, congratulations on the excellent content you cover and make available to our community.

    Can you help me with an M language case?

    I have a case here, where I’m trying some ways to solve the following problem:

    On 01/01/2023, the EBITDA value forecast for that day is entered into the “system”, and will serve as a premise for calculating days 02, 03, 04, etc.

    The problem is that this value from day 1 cannot be used on the following days (fill in downwards), that is, the result from day 2 must be the value in the net revenue column from day 2 + the EBITDA value from day 1.

    From then on, the EBITDA result for day 2 will be used to calculate net revenue for day 3, and so on for every day of the month.

    I’ve tried several ways, trying List.Acumulate, List.Range, List.Sum, but so far I haven’t been able to achieve this result.

    Do you have any idea how to solve this problem? Thank you very much in advance.

    Reply
  2. Wow! This is the most complete view of list function usage I’ve ever seen. Thank you! The more I used Power Query, the more I understand the benefits of using Lists and Records and yet I still feel like I’m only scratching the surface.

    One thing that always seems to trip me up however is how to escape the context of an each statement when iterating through an object. As an example, let’s say in each table row I have lists with a bunch of numbers in them and I essentially want to do a List.Transform([List Column], each _/[List Column]{0}) where it divides each number by the first number in the list. This doesn’t work but I seem to run into this often with iterators where I want to reference a value outside of the current context. Do you of a function/solution for this? I’ve done countless searches but I still haven’t found an answer to this seemingly foundational question about iteration within PQ.

    Thank you for your great resource! I can’t believe it’s taken me until now to find it!

    Reply
  3. Hi,

    I am new to M code and would like to achieve the following:

    I have a very large table with many columns and rows. I have a second table with n columns where each column represents a list (the columns are of irregular size). I want to filter the large table by the columns in the second table and output the results on different sheets with the names of the outputs being the column headers. All the elements of the first table not found will be sent to another sheet with a new nme (like Not found)

    Reply
  4. Helpful
    Base on your explanation of list i created those 2 step to change column datatype based on header label
    sharing if it could help someone else, certainly some clever way to do it

    // create list of header to be changed to datetime US, label with Date / TIMESTAMP
        filteredheaderlist = 
    Table.SelectRows(
        Table.FromList( Table.ColumnNames(#"Promoted Headers")), 
        each Text.Contains([Column1], "TIMESTAMP") 
               or Text.Contains([Column1], "DATE"))[Column1],
        // back to raw data to apply filtered header
        BacktoPromotedheader = #"Promoted Headers",
        // apply datatype per listed hearder matching
        Finaldatatypeforheader = 
    Table.TransformColumnTypes(
        BacktoPromotedheader, 
        List.Transform(filteredheaderlist, each {_, type datetime}), "en-US" )

    rgds

    Reply
  5. Thank you for this. How can I check if a current row value in a table column is in a list of values I specify.

    Following your examples here for both not List.Contains and not List.ContainsAny are returning errors. Please help.

    Reply
    • Agba,

      If you have a column called “Country” and you want to check whether it is one out of a selection of items, you can write:

      = List.Contains( { "France", "Brazil", "Spain" }, [Country] )

      It will return true when the value in the Country column (current row) has a value from the list in the first argument. Hope that helps!

      Reply
  6. Hi, Rick, very well written article. A lot of useful examples that show practical use of lists. Even though it does not contain much more text than official documentation on individual functions, I got considerable more from this article then from Microsoft documentation.

    One thought on List.Intersect, based on what you demonstrated on List.Contains function.

    From your example, List.Contains with custom equation criteria can be used for looking for partial match (even case-insensitive). One limitation, however, is that it enables you to search for one value only.

    I was wondering whether something more powerful could be achieved with List.Intersect.

    For example, I have a list of words with some containing diacritics. And I want to keep only those that contains the diacritics. When I saw your article I tried something like you can see below but it did not work. I am not sure whether it is not possible with List.Intersect or whether I used incorrect syntax.

    = List.Intersect(
       { { "Über", "back", "bell", "Fräuelin", "gruene" },
       { "ü", "ä", "ue"} },
       ( x, y ) => Text.Contains( Text.Lower( x ), Text.Lower( y ) )
    ) 

    I would like to have this result:

    = {"Über", "Fräuelin", "gruene"}
    

    Is there a relatively simple solution to this or a completely different approach is needed?

    Reply
    • Hi Andrej,

      You could try something like this, just note that it will only work for single character items in the LookFor list.

      [
        // list with strings to look in
        LookIn = { "Über", "back", "bell", "Fräuelein", "gruene" }, 
        // single character list to look for, so "ue" won't be found.
        LookFor = { "ü", "ä", "ue" }, 
        // convert LookIn strings into lower case and separate characters
        Split = List.Transform( LookIn, each Text.ToList( Text.Lower(_))),
        // see if any characters match what to LookFor
        Test = List.Transform(Split, each List.ContainsAny(_, LookFor) ), 
        // get the position of each true
        Pos = List.PositionOfAny(
                 Test, 
                 { true }, 
                 List.Count( List.Select( Test, each _ = true) ) ), 
        // get those positions from the LookIn list
        Result = try
          List.Generate(
            () => [v = LookIn{Pos{n}}, n = 0], 
            each [n] < List.Count(Pos), 
            each [v = LookIn{Pos{n}}, n = [n] + 1], 
            each [v]
          )
        otherwise
          {null}
      ][Result]
      Reply
    • Hi Andrej,

      After delving into the List.Accumulate function, I’ve found a crisp solution for your question. If you want to find the items in your list that have a text string in it, a single search would suffice with:

      List.Select(
        {"Über", "back", "bell", "Fräuelin", "gruene"}, 
        each Text.Contains(_, "Ü", Comparer.OrdinalIgnoreCase)
      )

      Now if you want to run this same logic for multiple values, you can call this function multiple times once for each value to search for. List.Accumulate is the function that can help you with that:

      List.Distinct(
        List.Accumulate(
          {"ü", "ä", "ue"}, 
          {}, 
          (state, current) =>
            state
              & List.Select(
                {"Über", "back", "bell", "Fräuelin", "gruene"}, 
                each Text.Contains(_, current, Comparer.OrdinalIgnoreCase)
              )
        )
      )

      As per your requirement this returns

       = {"Über", "Fräuelin", "gruene"} 

      For a better understanding of that function, please have a look at:
      https://gorilla.bi/power-query/list-accumulate/

      Reply
      • Hi Rick, thank you for looking into my question further. I found a workaround at that time, but this solution is much cleaner and more robust at the same time. Really appreciate the extra effort.

        Reply
    • Okay here are two other approaches:

      let
      LookIn = { "Über", "back", "bell", "Fräuelin", "gruene" },
      LookFor = List.Buffer({ "ü", "ä", "ue"}),
      Result = List.Select(LookIn, (x) => 
        List.AnyTrue(List.Transform(LookFor, each Text.Contains(Text.Lower(x),_))))
      in
      Result

      Or alternatively:

      let
      LookIn = { "Über", "back", "bell", "Fräuelin", "gruene" },
      LookFor = List.Buffer({ "ü", "ä", "ue"}),
      Result = List.Select(LookIn, 
         each List.Count(
             Splitter.SplitTextByAnyDelimiter( LookFor )(Text.Lower(_))) > 1 )
      in
      Result

      All credits go to Bill Szysz.
      Thank you for sharing your knowledge and amazing techniques Bill !

      Reply
  7. Great article. I have a question about working with lists from lists. I am trying to pull in a Microsoft List into PowerBI so that I can create some visuals. One of the visuals is a breakdown of how many times each possible choice selection in a particular column is selected. The challenge is that the choice column allows for multiple selections. I am new to PowerQuery and PowerBI and wandering how I need to transform the data coming in from the Microsoft List into my dataset to allow me to get the totals I want (if I just pull it in right now, using Microsoft Lists integration functionality with PowerBI, it sees each combination of selections as unique values, which is not what I am trying to achieve. Any suggestions on what I should do and how? Thank you.

    Reply
    • Hi David,

      I’m afraid I can’t help you here. Also, the Lists you are referring to are different from the lists this article refers to. Hope you solve it soon!

      Rick

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.