MissingField.UseNull

Updated on

MissingField.UseNull (2) is an enumeration that specifies the expected action for missing values in a row that contains columns less than expected. It is a member of the MissingField.Type and represents an optional parameter in record and table functions indicating that missing fields should be included as null values.

Examples

Let’s walk through an easy-to-understand scenario with the MissingField.UseNull enumeration in the Power Query M Language. Consider this dataset.

Missingfield.Type dataset

You can use Table.SelectColumns to select your desired columns.

Table.SelectColumns with a missing column

When you select a column name that does not exist in the dataset, Power Query throws an error.

Missingfield.Type demonstrating Table.SelectColumns

The default behavior of Power Query is to use the MissingField.Error enumeration. You can also explicitly tell Power Query to return missing columns and show null values. That’s what the enumeration MissingField.UseNull does. The function returns the number of columns provided in the selection argument. This behavior makes the enumeration a useful tool to return a fixed number of columns.

Missingfield.Type showing Missingfield.UseNull
= Table.SelectColumns( Source, {"Name", "Year"}, MissingField.UseNull )

In this example, we’re selecting “Name” and “Year” from the source table. Any missing columns Power Query will still return and add null values in its columns. It’s a useful when you want to enforce a fixed number of columns or want to add multiple columns at once.

Other related enumerations are:

Applies to

Here’s a list of functions that work with MissingField.Type:

Contribute » | Contributors: Rick de Groot