List Functions Overview

Updated on

Learn about List Functions in Power Query M Language, crucial when working with lists in your data manipulation tasks. This page provides a comprehensive list and descriptions of List Functions available in the M Language.

Table of contents

Generator Functions

FunctionDescriptionInputOutput
List.AccumulateAccumulates a summary value from the items in the list.ListAny
List.DateTimeZonesGenerates a list of datetimezone values given an initial value, count, and incremental duration value.DatetimezoneList
List.DateTimesGenerates a list of datetime values given an initial value, count, and incremental duration value.DatetimeList
List.DatesGenerates a list of date values given an initial value, count, and incremental duration value.DateList
List.DurationsGenerates a list of duration values given an initial value, count, and incremental duration value.DurationList
List.GenerateGenerates a list of values.FunctionList
List.NumbersReturns a list of numbers given an initial value, count, and optional increment value.NumberList
List.RandomReturns a list of random numbers.NumberList
List.RepeatReturns a list that is count repetitions of the original list.ListList
List.TimesGenerates a list of time values given an initial value, count, and incremental duration value.TimeList

Information Functions

FunctionDescriptionInputOutput
List.AllTrueReturns true if all expressions are true.ListLogical
List.AnyTrueReturns true if any expression is true.ListLogical
List.ContainsIndicates whether the list contains the value.ListLogical
List.ContainsAllIndicates where a list includes all the values in another list.ListLogical
List.ContainsAnyIndicates where a list includes any of the values in another list.ListLogical
List.IsDistinctIndicates whether there are duplicates in the list.ListLogical
List.IsEmptyReturns true if the list is empty.ListLogical
List.MatchesAllReturns true if the condition function is satisfied by all values in the list.ListLogical
List.MatchesAnyReturns true if the condition function is satisfied by any value.ListLogical
List.PositionOfReturns the offset(s) of a value in a list.ListAny
List.PositionOfAnyReturns the first offset of a value in a list.ListAny
List.PositionsReturns a list of offsets for the input.ListList

Selection Functions

FunctionDescriptionInputOutput
List.AlternateReturns a list comprised of all the odd numbered offset elements in a list.ListList
List.BufferBuffers a list.ListList
List.FindTextReturns a list of values (including record fields) that contain the specified text.ListList
List.FirstReturns the first value of the list or the specified default if empty.ListAny
List.FirstNReturns the first set of items in the list by specifying how many items to return or a qualifying condition.ListAny
List.LastReturns the last value of the list or the specified default if empty.ListAny
List.LastNReturns the last value in the listListAny
List.MaxReturns the maximum value or the default value for an empty list.ListAny
List.MaxNReturns the maximum value(s) in the listListList
List.MinReturns the minimum value or the default value for an empty list.ListAny
List.MinNReturns the minimum value(s) in the listListList
List.RangeReturns a subset of the list beginning at an offset.ListList
List.SelectReturns a list of values that match the condition.ListList
List.SingleReturns the one list item for a list of length one, otherwise throws an exception.ListAny
List.SingleOrDefaultReturns the one list item for a list of length one and the default value for an empty list.ListAny

Statistical Functions

FunctionDescriptionInputOutput
List.AverageReturns the average of the valuesListAny
List.CountReturns the number of items in the list.ListNumber
List.CovarianceReturns the covariance between the two lists of numbers.ListNumber
List.MedianReturns the median value in the list.ListAny
List.ModeReturns the most frequent value in the list.ListAny
List.ModesReturns a list of the most frequent values in the list.ListList
List.NonNullCountReturns the number of non-null items in the list.ListNumber
List.PercentileReturns one or more sample percentiles corresponding to the given probabilities.ListAny
List.ProductReturns the product of the numbers in the list.ListNumber
List.StandardDeviationReturns a sample based estimate of the standard deviation.ListNumber
List.SumReturns the sum of the items in the list.ListAny

Transformation Functions

FunctionDescriptionInputOutput
List.CombineReturns a single list by combining multiple lists.ListList
List.ConformToPageReaderThis function is intended for internal use only.ListTable
List.DifferenceReturns the difference of the two given lists.ListList
List.DistinctReturns a list of values with duplicates removed.ListList
List.InsertRangeInserts values into a list at the given index.ListList
List.IntersectReturns the intersection of the list values found in the input.ListList
List.RemoveFirstNReturns a list that skips the specified number of elements at the beginning of the list.ListList
List.RemoveItemsRemoves items from list1 that are present in list.ListList
List.RemoveLastNReturns a list that removes the specified number of elements from the end of the list.ListList
List.RemoveMatchingItemsRemoves all occurrences of the input values.ListList
List.RemoveNullsRemoves all “null” values from the specified list.ListList
List.RemoveRangeRemoves count number of values starting at the specified position.ListList
List.ReplaceMatchingItemsApplies each replacement of { old, new }.ListList
List.ReplaceRangeReplaces count number of values starting at position with the replacement values.ListList
List.ReplaceValueSearches a list for the specified value and replaces it.ListList
List.ReverseReverses the order of values in the list.ListList
List.SkipReturns a list that skips the specified number of elements at the beginning of the list.ListList
List.SortSorts a list of data according to the criteria specified.ListList
List.SplitSplits the specified list into a list of lists using the specified page size.ListList
List.TransformReturns a new list of values computed from this list.ListList
List.TransformManyReturns a list whose elements are transformed from the input list using specified functions.ListList
List.UnionReturns the union of the list values found in the input.ListList
List.ZipReturns a list of lists by combining items at the same position in multiple lists.ListList

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-functions