List.NonNullCount

As you can see, this function takes a single argument - a list - and returns a number that represents the count of non-null values in that list. Here's an example of how this function can be used:

In this example, we define a list called `myList` that contains six values, two of which are null. We then use the List.NonNullCount function to count the number of non-null values in this list, which is four. Finally, we assign this count to a variable called `count` and return it.

Using List.NonNullCount in practice

Now that we understand the basics of how the List.NonNullCount function works, let's explore some real-world applications for this function.

Cleaning up messy data

One of the most common use cases for the List.NonNullCount function is cleaning up messy data. When working with large datasets, it's not uncommon to encounter null values or other types of data that need to be cleaned up or removed. The List.NonNullCount function can be used to quickly identify the number of non-null values in a particular column or dataset, which can then be used as a guide for cleaning up the data.

Validating data quality

Another use case for the List.NonNullCount function is validating data quality. In some cases, it may be important to ensure that certain columns or datasets contain a minimum number of non-null values in order to be considered valid or useful. The List.NonNullCount function can be used to quickly and easily validate the quality of data by counting the number of non-null values and comparing it to a predetermined threshold.

Creating custom functions

Finally, the List.NonNullCount function can be used as a building block for creating custom functions. For example, if you frequently need to count the number of non-null values in a particular column or dataset, you could create a custom function that uses the List.NonNullCount function to do this automatically. This can save time and simplify your workflow, especially if you are working with large or complex datasets.

The List.NonNullCount function is a simple but powerful tool that can be used to quickly and easily count the number of non-null values in a list. Whether you are cleaning up messy data, validating data quality, or creating custom functions, this function is a valuable addition to any Power Query user's toolkit. By understanding the M code behind this function and exploring its many applications, you can become a more efficient and effective data analyst.

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

Upcoming Courses

Contact Us