List.RemoveNulls

Updated on
Rick de Groot

List.RemoveNulls is a Power Query M function that removes all occurrences of “null” values in a list. The function returns a modified list with the null values removed.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.RemoveNulls( list as list ) as list

Description

The List.RemoveNulls function eliminates all instances of “null” values. If your list doesn’t have any “null” values to begin with, the function returns the original list, untouched.

Examples

Let’s say you have a list of numbers, but some of the entries are “null,” which could interfere with any calculations you might want to perform. Here’s how you can remove those pesky “null” values:

// Output: { 1, 2, 0, 1, 2 }
List.RemoveNulls( { 1, null, 2, null, 0, 1, 2 } )

In this example, the function takes the original list and returns a new one, stripped of any “null” values.

When No Null Values Are Present

What if your list is already clean, with no “null” values? Well, the function won’t make any changes. It will simply return the original list.

// Output: { 1, 2, 4, 5 }
List.RemoveNulls( { 1, 2, 4, 5 } )

Real-World Application: Cleaning Up Sales Data

Imagine you have a list of monthly sales figures, and some months have “null” values due to incomplete data entry. You want to compute the average sales value, but null values skew the average calculation. Using List.RemoveNulls, you can easily clean up the list to perform accurate calculations like averages or sums.

let
  CleanList = List.RemoveNulls( { 1, null, 2, null, 0, 1, 2 } ), // Output: { 1, 2, 0, 1, 2 }
  ListAvg = List.Average( CleanList)                             // Output 1.2
in 
  ListAvg

In this example, we first use List.RemoveNulls to get rid of any “null” values. Then, we calculate the average of the cleaned-up list using List.Average. The result is a more accurate average sales figure.

By understanding how to effectively use the List.RemoveNulls function, you can ensure that your data is clean and ready for analysis.

Learn more about List.RemoveNulls in the following articles:

Other functions related to List.RemoveNulls are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy