The M Code Behind the Power Query M function List.IsEmpty

Understanding the List.IsEmpty Function

Before we dive into the technicalities, let us first comprehend the `List.IsEmpty` function’s basic concept. It is a simple yet powerful function that returns true if a list or table is empty and false if it is not. The following example demonstrates the function’s use:

= List.IsEmpty({})

Here, the function returns true because the curly brackets {} define an empty list. Similarly, the following function returns false:

= List.IsEmpty({1,2,3})

Here, the function returns false because the list contains elements. The `List.IsEmpty` function can also be used with tables and records, as illustrated in the following examples:

= List.IsEmpty(#table({},{}))

The above function returns true because the table contains no rows or columns. On the other hand, the following function returns false:

= List.IsEmpty([Name = “John”, Age = 25])

Here, the function returns false because the record contains values.

The M Code Behind List.IsEmpty

Now that we have established the `List.IsEmpty` function's purpose let's take a closer look at the M code behind it. The function's M code is as follows:

(list) => Value.Is(null, list, (x) => (List.Count(x) = 0))

Here, the `list` parameter is the object being evaluated for emptiness. The `Value.Is` function checks whether the `list` parameter is null and returns the result. If the parameter is not null, the `List.Count` function counts the number of items in the list. If the count is zero, the function returns true, indicating that the list is empty. Otherwise, it returns false, indicating that the list is not empty.

Practical Applications of List.IsEmpty

The `List.IsEmpty` function can be used for various tasks, including data cleaning and filtering. When working with large datasets, identifying empty lists or tables is a crucial step in data preparation. The following example demonstrates how the `List.IsEmpty` function can be used to filter data:

Suppose we have a table containing a list of employees' names and their respective salaries. We want to filter out all employees who have no salary. We can use the `List.IsEmpty` function to identify all the rows with empty salary fields and remove them from the data set. The following M code achieves this:

= Table.SelectRows(#”SourceTable”, each not List.IsEmpty([Salary]))

Here, `#"SourceTable"` refers to the original data source. The `Table.SelectRows` function filters the table, and the `List.IsEmpty` function identifies the rows with empty salary fields. The `not` function negates the `List.IsEmpty` filter so that only rows with non-empty salary fields are returned.

The `List.IsEmpty` function is a handy tool for managing data in Power Query. It is used to determine if a list, table, or record is empty and can be used for filtering and data cleaning. The function's M code is straightforward and evaluates the object being assessed for emptiness by counting its contents. Through practical applications, we have demonstrated how the `List.IsEmpty` function can be used to filter data sets and remove empty rows. By leveraging this function's capabilities, developers can significantly improve their data processing workflows.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

10-12 Jul 23 (London or Online)
25-27 Sep 23 (London or Online)
20-22 Nov 23 (London or Online)

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)