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

Understanding the M Code Behind List.RemoveNulls

Before we dive into the M code behind `List.RemoveNulls`, let’s first take a look at what this function does. The `List.RemoveNulls` function takes a list as its input and returns a new list with all null values removed. Here is the syntax for the `List.RemoveNulls` function:

List.RemoveNulls(list as list) as list

Now let's take a closer look at the M code behind this function. The M code for `List.RemoveNulls` is fairly straightforward. Here is the code:

(list) => List.Select(list, each _ <> null)

Let's break down this code. The `List.Select` function is used to filter out null values from the input list. The second argument of the `List.Select` function is a logical expression that determines whether each item in the list should be included in the output list. In this case, the logical expression is `each _ <> null`, which means that any item in the list that is not null will be included in the output list.

Use Cases for List.RemoveNulls

Now that we understand the M code behind `List.RemoveNulls`, let's explore some use cases for this function.

Cleaning Up Data

One of the most common use cases for `List.RemoveNulls` is to clean up data. When working with large datasets, it is not uncommon to encounter null values in your data. These null values can cause issues when analyzing or visualizing your data. By using `List.RemoveNulls`, you can quickly and easily remove null values from your data, making it easier to work with.

Creating Custom Functions

Another use case for `List.RemoveNulls` is to create custom functions that perform more complex data cleaning tasks. For example, you could create a custom function that removes null values from a specific column in a table. By using `List.RemoveNulls` within your custom function, you can quickly and easily clean up your data without having to write complex M code from scratch.

Filtering Lists

Finally, `List.RemoveNulls` can also be used to filter lists based on specific criteria. For example, you could use `List.RemoveNulls` to filter a list of customer names and addresses to only include records where the address is not null. By doing so, you can quickly and easily create more targeted lists that are tailored to your specific needs.

In conclusion, `List.RemoveNulls` is a powerful function in Power Query M that can be used to quickly and easily remove null values from lists. By understanding the M code behind this function and exploring some use cases for it, you can gain a deeper understanding of how it works and how it can be used to streamline your data cleaning processes.

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

Upcoming Courses

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)