Record.FieldOrDefault

Record.FieldOrDefault is a Power Query M function that returns the value of the specified field in a record or a default value if the field is not found. The function returns the value of the field or the optional defaultValue if the field is not present.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Record.FieldOrDefault(
   record as nullable record,
   field as text,
   optional defaultValue as any,
) as any

Description

Record.FieldOrDefault fetches the value of a named field within a record, or returns a defaultValue if the field is absent. This function ensures a fallback value can be used, avoiding errors when a field is not found.

Examples

Let’s see this function in action with some practical examples.

In it’s most basic form, the Record.FieldOrDefault function grabs a value from a record. For instance, suppose you have a record that contains country abbreviations and the full country names. Imagine the following record:

let 
  countryRecord = 
    [ UK  = "United Kingdom",  
      NL  = "Netherlands",
      FRA = "France",
      BE  = "Belgium",
      ITA = "Italy"           ]
in
  countryRecord

You can retrieve the full name of the country abbreviation NL by using the Record.FieldOrDefault function and providing the field name, in this case the Abbreviation, in its second argument:

// Output: "Netherlands"
let
  countryRecord = 
    [ UK  = "United Kingdom",  
      NL  = "Netherlands",
      FRA = "France",
      BE  = "Belgium",
      ITA = "Italy"           ],
  lookupCountry = Record.FieldOrDefault( countryRecord, "NL" )
in
  lookupCountry

The code performs a lookup operation by searching for NL within the field names of the record, and returns the corresponding field value.

If we would have done this manually, the code would have looked something like:

let
  countryAbbrevation = "NL",
  conditions =
    if [Abbreviation] = "UK"  then "United Kingdom"
      else if [Abbreviation] = "NL"  then "Netherlands"
      else if [Abbreviation] = "FRA" then "France"
      else if [Abbreviation] = "BE"  then "Belgium"
      else if [Abbreviation] = "ITA" then "Italy"
      else "Other"
in
  conditions

This code, while valid, repeats the if-then-else statement repeatedly.

Providing a Default Fallback Value

In some cases, the lookup operation may not find a match. If we then leave the above code as is, you would get an error. However, you can also provide a default fallback value for the situation where Record.FieldOrDefault does not find a match. You can use the third argument for this.

For instance, take a look at the below example:

// Output: "Other"
let
  countryRecord = 
    [ UK  = "United Kingdom",  
      NL  = "Netherlands",
      FRA = "France",
      BE  = "Belgium",
      ITA = "Italy"           ],
  lookupCountry = Record.FieldOrDefault( countryRecord, "SW", "Other")
in
  lookupCountry

Here, the Record.FieldOrDefault function tries to look for the abbreviation SW, but doesn’t find it. It then returns the default value Other as provided in the third argument.

Related functions

Other functions related to Record.FieldOrDefault are:

• Record.Field
• Record.SelectFields

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/record-fieldordefault