List.NonNullCount

Understanding the List.NonNullCount function

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. Here’s the M code for this function:

List.NonNullCount(list as list) as number

``` 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: ```

let

myList = {1, 2, null, 4, null, 6},

count = List.NonNullCount(myList)

in

count

``` 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) Power Query and M Intensive Training Course £1,260.00 – £31,860.00 Select optionsContinue Loading Done Power Query and M Introduction £474.00 – £11,700.00 Select optionsContinue Loading Done Power Query and M Intermediate £474.00 – £11,700.00 Select optionsContinue Loading Done Power Query and M Advanced £474.00 – £11,700.00 Select optionsContinue Loading Done Upcoming Courses Contact Us Subject Your Name (required) Company/Organisation Email (required) Telephone Training Course(s) Power BI Intensive TrainingPower BI introduction Power BI IntermediatePower BI AdvancedDAXPower Query MPower BI CertificationPower BI AdministrationPower PlatformPower AutomatePower AppsOTHER Your Message Upload Example Document(s) (Zip multiple files) ```
``` ```
``` ```
``` ```
``` ```
``` ```