M Query Guide

Functions

M List Functions

List Functions

M list functions like List.Sum aggregate collections.

Introduction to M List Functions

M List Functions in Power Query provide powerful tools for manipulating collections of data. These functions allow you to perform a variety of operations on lists, such as aggregation, transformation, and filtering. In this guide, we'll explore some of the most commonly used M List Functions with examples.

Basic List Functions

List functions operate on list data structures. A list in Power Query is a collection of items that can be numbers, text, records, or even other lists. Here are some basic list functions:

  • List.Sum(list as list) as number: Returns the sum of the numbers in the list.
  • List.Count(list as list) as number: Returns the number of items in the list.
  • List.Contains(list as list, value as any) as logical: Checks if a value exists in the list.

Working with List Transformations

Transforming lists is a common task. Functions like List.Transform and List.Generate are essential for these operations:

  • List.Transform(list as list, transform as function) as list: Applies a specified transformation function to each item in the list.
  • List.Generate(initial as function, condition as function, next as function, optional selector as function) as list: Generates a list using the specified functions for initialization, condition, and next value.

Advanced List Operations

Advanced list operations provide more control over list manipulation. Some useful functions include:

  • List.Select(list as list, selection as function) as list: Filters a list based on a selection function.
  • List.Sort(list as list, optional comparisonCriteria as any, optional order as nullable number) as list: Sorts a list according to specified criteria.

Conclusion and Best Practices

M List Functions are versatile tools in Power Query that enhance your data manipulation capabilities. By understanding and utilizing these functions, you can efficiently handle lists to perform operations like aggregation, transformation, and filtering. Always ensure your lists are appropriately structured to leverage these functions effectively.

Functions

Next
Table Functions