Record.FieldOrDefault

The first parameter, record, specifies the record from which to retrieve the field value. The second parameter, field, specifies the name of the field to retrieve. The optional third parameter, default, specifies the default value to use if the field is missing. If the default parameter is not specified, the function will return null if the field does not exist.

Here is an example of how Record.FieldOrDefault can be used to retrieve a value from a record:

In this example, we create a record called myRecord with two fields: Name and Age. We then use Record.FieldOrDefault to retrieve the value of the Name field, specifying a default value of "Unknown" in case the field does not exist. We also use Record.FieldOrDefault to retrieve the value of the Address field, which does not exist in the record, so the default value of "Unknown" is returned. Finally, we return a record containing the values of both fields.

How can Record.FieldOrDefault be used in data transformations?

Record.FieldOrDefault can be used in a variety of ways to transform data in Power Query. Here are a few examples:

Handling missing fields

As mentioned earlier, Record.FieldOrDefault can be used to handle missing fields in a record by specifying a default value. This is particularly useful when working with data that may not be fully standardized. For example, suppose you have a table of customer data, and some records have a "Phone" field while others do not. You can use Record.FieldOrDefault to retrieve the phone number, specifying a default value of null or an empty string if the field is missing.

In this example, we create a table with two records, one of which has a "Phone" field and one of which does not. We use Table.TransformColumns to apply a transformation function to the Phone column, which uses Record.FieldOrDefault to retrieve the value of the "Phone" field, specifying an empty string as the default value.

Handling missing records

In addition to handling missing fields, Record.FieldOrDefault can also be used to handle missing records. Suppose you have two tables of customer data, one with a "Phone" field and one without, and you want to merge them into a single table. You can use Record.FieldOrDefault to retrieve the phone number, specifying a default value of null or an empty string if the record does not exist.

In this example, we create two tables, one with a "Phone" field and one without. We then use Table.NestedJoin to merge the tables on the "Name" column, creating a new column called "NewColumn" that contains the merged records. We use Table.TransformColumns to apply a transformation function to the "NewColumn" column, which uses Record.FieldOrDefault to retrieve the value of the "Phone" field from the source1 record, specifying an empty string as the default value.

Transforming nested records

Record.FieldOrDefault can also be used to transform nested records. Suppose you have a table of employee data, where each record contains a nested record with job information. You can use Record.FieldOrDefault to retrieve the job title, specifying a default value of null or an empty string if the nested record does not exist.

In this example, we create a table with two records, one of which has a nested record with job information and one of which does not. We use Table.TransformColumns to apply a transformation function to the Job column, which uses Record.FieldOrDefault to retrieve the value of the "Title" field from the nested record, specifying an empty string as the default value.

Record.FieldOrDefault is a powerful function in the M language that allows you to retrieve values from records while handling missing fields and records. By understanding the M code behind this function and how it can be used in data transformations, you can become a more proficient Power Query user and streamline your data wrangling process.

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

Upcoming Courses

Contact Us