List functions - PowerQuery M (2024)

Article
10/10/2022

These functions create and manipulate list values.

Information

Name	Description
List.Count	Returns the number of items in a list.
List.NonNullCount	Returns the number of items in a list excluding null values
List.IsEmpty	Returns whether a list is empty.

Selection

Name	Description
List.Alternate	Returns a list with the items alternated from the original list based on a count, optional repeatInterval, and an optional offset.
List.Buffer	Buffers the list in memory. The result of this call is a stable list, which means it will have a determinimic count, and order of items.
List.Distinct	Filters a list down by removing duplicates. An optional equation criteria value can be specified to control equality comparison. The first value from each equality group is chosen.
List.FindText	Searches a list of values, including record fields, for a text value.
List.First	Returns the first value of the list or the specified default if empty. Returns the first item in the list, or the optional default value, if the list is empty. If the list is empty and a default value is not specified, the function returns.
List.FirstN	Returns the first set of items in the list by specifying how many items to return or a qualifying condition provided by `countOrCondition`.
List.InsertRange	Inserts items from values at the given index in the input list.
List.IsDistinct	Returns whether a list is distinct.
List.Last	Returns the last set of items in the list by specifying how many items to return or a qualifying condition provided by `countOrCondition`.
List.LastN	Returns the last set of items in a list by specifying how many items to return or a qualifying condition.
List.MatchesAll	Returns true if all items in a list meet a condition.
List.MatchesAny	Returns true if any item in a list meets a condition.
List.Positions	Returns a list of positions for an input list.
List.Range	Returns a count items starting at an offset.
List.Select	Selects the items that match a condition.
List.Single	Returns the single item of the list or throws an `Expression.Error` if the list has more than one item.
List.SingleOrDefault	Returns a single item from a list.
List.Skip	Skips the first item of the list. Given an empty list, it returns an empty list. This function takes an optional parameter `countOrCondition` to support skipping multiple values.

Transformation functions

Name	Description
List.Accumulate	Accumulates a result from the list. Starting from the initial value seed this function applies the accumulator function and returns the final result.
List.Combine	Merges a list of lists into single list.
List.ConformToPageReader	This function is intended for internal use only.
List.RemoveRange	Returns a list that removes count items starting at offset. The default count is 1.
List.RemoveFirstN	Returns a list with the specified number of elements removed from the list starting at the first element. The number of elements removed depends on the optional `countOrCondition` parameter.
List.RemoveItems	Removes items from `list1` that are present in `list2`, and returns a new list.
List.RemoveLastN	Returns a list with the specified number of elements removed from the list starting at the last element. The number of elements removed depends on the optional `countOrCondition` parameter.
List.Repeat	Returns a list that repeats the contents of an input list count times.
List.ReplaceRange	Returns a list that replaces count values in a list with a `replaceWith` list starting at an index.
List.RemoveMatchingItems	Removes all occurrences of the given values in the list.
List.RemoveNulls	Removes null values from a list.
List.ReplaceMatchingItems	Replaces occurrences of existing values in the list with new values using the provided `equationCriteria`. Old and new values are provided by the `replacements` parameters. An optional equation criteria value can be specified to control equality comparisons. For details of replacement operations and equation criteria, go to Parameter values.
List.ReplaceValue	Searches a list of values for the value and replaces each occurrence with the replacement value.
List.Reverse	Returns a list that reverses the items in a list.
List.Split	Splits the specified list into a list of lists using the specified page size.
List.Transform	Performs the function on each item in the list and returns the new list.
List.TransformMany	Returns a list whose elements are projected from the input list.

Membership functions

Since all values can be tested for equality, these functions can operate over heterogeneous lists.

Name	Description
List.AllTrue	Returns true if all expressions in a list are true
List.AnyTrue	Returns true if any expression in a list in true
List.Contains	Returns true if a value is found in a list.
List.ContainsAll	Returns true if all items in values are found in a list.
List.ContainsAny	Returns true if any item in values is found in a list.
List.PositionOf	Finds the first occurrence of a value in a list and returns its position.
List.PositionOfAny	Finds the first occurrence of any value in values and returns its position.

Set operations

Name	Description
List.Difference	Returns the items in list 1 that do not appear in list 2. Duplicate values are supported.
List.Intersect	Returns a list from a list of lists and intersects common items in individual lists. Duplicate values are supported.
List.Union	Returns a list from a list of lists and unions the items in the individual lists. The returned list contains all items in any input lists. Duplicate values are matched as part of the Union.
List.Zip	Returns a list of lists combining items at the same position.

Ordering

Ordering functions perform comparisons. All values that are compared must be comparable with each other. This means they must all come from the same datatype (or include null, which always compares smallest). Otherwise, an Expression.Error is thrown.

Comparable data types include:

Number
Duration
DateTime
Text
Logical
Null

Name	Description
List.Max	Returns the maximum item in a list, or the optional default value if the list is empty.
List.MaxN	Returns the maximum values in the list. The number of values to return or a filtering condition must be specified.
List.Median	Returns the median item from a list.
List.Min	Returns the minimum item in a list, or the optional default value if the list is empty.
List.MinN	Returns the minimum values in a list. The number of values to return or a filtering condition may be specified.
List.Sort	Returns a sorted list using comparison criterion.
List.Percentile	Returns one or more sample percentiles corresponding to the given probabilities.

Averages

These functions operate over hom*ogeneous lists of Numbers, DateTimes, and Durations.

Name	Description
List.Average	Returns an average value from a list in the datatype of the values in the list.
List.Mode	Returns an item that appears most commonly in a list.
List.Modes	Returns all items that appear with the same maximum frequency.
List.StandardDeviation	Returns the standard deviation from a list of values. List.StandardDeviation performs a sample based estimate. The result is a number for numbers, and a duration for DateTimes and Durations.

Addition

These functions work over hom*ogeneous lists of Numbers or Durations.

Name	Description
List.Sum	Returns the sum from a list.

Numerics

These functions only work over numbers.

Name	Description
List.Covariance	Returns the covariance from two lists as a number.
List.Product	Returns the product from a list of numbers.

Generators

These functions generate list of values.

Name	Description
List.Dates	Returns a list of date values from size count, starting at start and adds an increment to every value.
List.DateTimes	Returns a list of datetime values from size count, starting at start and adds an increment to every value.
List.DateTimeZones	Returns a list of of datetimezone values from size count, starting at start and adds an increment to every value.
List.Durations	Returns a list of durations values from size count, starting at start and adds an increment to every value.
List.Generate	Generates a list of values.
List.Numbers	Returns a list of numbers from size count starting at initial, and adds an increment. The increment defaults to 1.
List.Random	Returns a list of count random numbers, with an optional seed parameter.
List.Times	Returns a list of time values of size count, starting at start.

Parameter values

Occurrence specification

Occurrence.First = 0;
Occurrence.Last = 1;
Occurrence.All = 2;

Sort order

Order.Ascending = 0;
Order.Descending = 1;

Equation criteria

Equation criteria for list values can be specified as either:

A function value that is either:
- A key selector that determines the value in the list to apply the equality criteria.
- A comparer function that is used to specify the kind of comparison to apply. Built in comparer functions can be specified—go to Comparer functions.
A list value that has:
- Exactly two items.
- The first element is the key selector as specified above.
- The second element is a comparer as specified above.

For more information and examples, go to List.Distinct.

Comparison criteria

Comparison criterion can be provided as either of the following values:

A number value to specify a sort order. For more information, go to Sort order.
To compute a key to be used for sorting, a function of one argument can be used.
To both select a key and control order, comparison criterion can be a list containing the key and order.
To completely control the comparison, a function of two arguments can be used that returns -1, 0, or 1 given the relationship between the left and right inputs. Value.Compare is a method that can be used to delegate this logic.

For more information and examples, go to List.Sort.

Replacement operations

Replacement operations are specified by a list value. Each item of this list must be:

A list value of exactly two items.
First item is the old value in the list, to be replaced.
Second item is the new value, which should replace all occurrences of the old value in the list.