MissingField option

In this article, we will dive deep into the M code behind the Power Query M function MissingField option and understand how it works in real-world scenarios.

Introduction to MissingField option in Power Query

The MissingField option is a useful feature in Power Query that allows users to handle missing values in their data. When working with data, it is common to come across missing values, either due to human error, system limitations, or other factors. These missing values can cause inconsistencies and inaccuracies in data analysis, and hence, it is important to handle them effectively.

The MissingField option in Power Query allows users to specify how they want to handle the missing values. The option can be used to replace the missing values with a default value, remove the rows with missing values, or apply a custom transformation to the missing values.

How to use MissingField option in Power Query

To use the MissingField option in Power Query, users need to follow the below steps:

1. Load the data into Power Query

2. Select the column(s) with missing values

3. Click on the Transform tab

4. Click on the MissingField option under the Any Column section

5. Select the appropriate option from the drop-down menu

Users can select one of the following options:

– Replace with default value: Users can replace the missing values with a default value of their choice. For example, they can replace the missing values with 0, NA, or any other value.

– Remove rows: Users can remove the rows with missing values from their data. This option is useful when the missing values are few and do not impact the overall analysis significantly.

– Custom transformation: Users can apply a custom transformation to the missing values. For example, they can replace the missing values with the average of the non-missing values in the same column.

The M code behind the MissingField option

The MissingField option in Power Query is powered by the M language, which is a functional programming language used in Power Query. The M language provides users with a wide range of functions and options to manipulate and transform data.

The M code behind the MissingField option is based on a simple logic that checks for the missing values in the selected column(s) and applies the selected option to handle the missing values.

For example, if the user selects the “Replace with default value” option and enters 0 as the default value, the M code will look something like this:

= Table.ReplaceValue(#”Previous Step”,null,0,Replacer.ReplaceValue,{“Column1″,”Column2”})

= Table.SelectRows(#”Previous Step”, each not List.ContainsAny(Record.FieldValues(_),{null}))