MissingField option

D

T

The M Code Behind the Power Query M function 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”})


The above code replaces the missing values in "Column1" and "Column2" with 0.

Similarly, if the user selects the "Remove rows" option, the M code will look something like this:


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


The above code removes all the rows with missing values from the selected column(s).

Real-world scenarios for using MissingField option

The MissingField option in Power Query is a powerful feature that can be used to handle missing values in a wide range of real-world scenarios. Below are some scenarios where the MissingField option can be useful:

1. Handling missing values in survey data: When conducting surveys, it is common to have missing values due to non-response or incomplete responses. The MissingField option can be used to replace the missing values with the mean or median value of the other responses in the same column.

2. Handling missing values in financial data: In financial data analysis, missing values can cause significant inaccuracies. The MissingField option can be used to replace the missing values with the last known value, or with the average value of the non-missing values in the same column.

3. Handling missing values in marketing data: In marketing data analysis, missing values can impact the accuracy of customer segmentation and targeting. The MissingField option can be used to remove the rows with missing values or replace the missing values with the mean or median value of the non-missing values in the same column.

In conclusion, the MissingField option in Power Query is a powerful feature that allows users to handle missing values in their data effectively. The M code behind the MissingField option is based on a simple logic that checks for missing values and applies the selected option to handle them. Real-world scenarios for using the MissingField option range from survey data to financial data and marketing data. By using the MissingField option, users can ensure accurate and consistent data analysis.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)