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 objectsGenerate 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 }
= List.Numbers( 1, 5, 0.1 ) // Equals { 1, 1.1, 1.2, 1.3, 1.4 }
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 createAny 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 aboveConsecutive 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 dayAlternatively, 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 listIf 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 zCustom 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 1A 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 sameWhen 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 nullWorking 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 capitalizationAlternatively, 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 capitalizationIn 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'sYou 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.
- the original list
- one or more lists of two values with the old value and the new value, provided in a list
- 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 2However, 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 CapitalsThe 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 trueThis 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 falseReturn 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 orderPerform 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,9518Even 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!
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Hello Mr. Rick,
I regularly watch your videos, especially those on Power Query and Power BI and I truly enjoy them. I’ve learned a lot of new tricks from your content.
In your blog, under the section on Consecutive Numbers, I believe there might be a small amendment needed. Specifically, I would recommend checking the output of the example `List.Numbers(1, 5, -1)`.
Thank you for all the valuable insights you share, and I appreciate your attention to this detail.
Thanks.
Hi, I’d like an efficient function to search a string made of both specific characters and wildcards in a LookIn text.
For instance, after having defined :
# = any digit, i.e # = {“0”..”9″}
$ = any optional digit $ = {“0”..”9″, “”)
@ = any lower letter @ = {“a”.. “z”}
ç = any capital letter ç = {“A”..”Z”}
* = any optional generic letter like * = {“a”..”z”, “A”..”Z”, “”}
(other possible definitions for ponctuation and separators)
Find in LookIn text all occurrences of string
string = “ç** num ##_##”
This should find results like
“ABC num 15_27” or
“Ab num 41_12”
but not
“ab num 41_12”
Do you have any good algorithm to propose? So far my attempts that look for all possible combinations of characters are far too much slow
Thanks
Andrea
I am attempting to create a new column in a power query table. The table has two existing columns named “Day” and “ES”. Each column contains nested lists in their respective rows. I want to take the list from the “DAY” column for each row and transform it to a true/false list that compares each value in the Day list with the much shorter list in each “ES” row. I have attempted this with the following :
but this is not working as it produces a list for each item in “DAY” lists as “ERROR” When I use a similar code that reads a hard coded list, example {3,4,5}, the query works and produces a new nested list of true false values. the M code that works follows:
What needs to change in my first code snippet so that it will work? I feel like this should be simpler. I have scoured tutorials and I believe I am missing something basic about working with nested lists.
Hey Aaron,
The ‘each’ statement is syntax sugar for a function. It creates a function with a single parameter name, which is the underscore.
So:
Is the same as:
I haven’t tried your code. But when using the each statement twice, the variable ‘_’ is also defined twice, which creates a conflict.
Please adjust your code to something like:
In the new case, there is no conflict between variable names.
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.
Loved it!
Great article! Is there a way to remove several values by using indexes in a list?
Hey Marc –
I’ve tried doing something similar in this article:
https://gorilla.bi/power-query/wildcards/#multiple-single-character-wildcards
There’s no build-in function but you can try this approach:
In the above example the [Word] column contains the text to remove characters from.
Edit: Read this article for an explanation how you can achieve this: https://gorilla.bi/power-query/remove-list-items-by-positions/
Hi Rick.
This is very informative article. It’s really helpful for me. Keep going.
Love from Indonesia
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!
Walter – that’s a very frustrating point indeed. It’s something I blogged about here: https://gorilla.bi/power-query/join-on-date-range/. You’d have to define a function to refer to the outside scope.
Rick
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)
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
rgds
Excellent Article. Thanks Rick for sharing.
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.
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:
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!
Nice article on list. Book marked the page and subscribed to your youtube channel.
Great & complete article – for sure I will keep it as reference/loopback when needed. Thanks Rick
very good effort
Very useful article, very much appreciated !
Happy to hear you like it!
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.
I would like to have this result:
Is there a relatively simple solution to this or a completely different approach is needed?
Hi Andrej,
You could try something like this, just note that it will only work for single character items in the LookFor list.
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:
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:
As per your requirement this returns
For a better understanding of that function, please have a look at:
https://gorilla.bi/power-query/list-accumulate/
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.
Okay here are two other approaches:
Or alternatively:
All credits go to Bill Szysz.
Thank you for sharing your knowledge and amazing techniques Bill !
Thanks Melissa, these approaches are a piece of art. Glad you were willing to share them 🙏
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.
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
Man, this is a great comprehensive article. Great job, looking forward to your next posts!
Wao just amazing and learnt too many things .Sir please also share about record and table data type in power querry .
Sure! Record and Table functions may come later. I released a post on Text Functions here:
https://gorilla.bi/power-query/text-functions/
And you can now also find the post on tables right here:
https://gorilla.bi/power-query/creating-tables/
Enjoy!