Understanding Records in Power Query M

Records serve as one of the structured values in Power Query. Unlike lists that hold a series of values in a specific order, records take a step further by associating each value with a unique key, making data retrieval and manipulation more organized.

The following sections will delve into creating records, accessing fields within them, and manipulating them through various operators.

Introduction to Records

You can view a record as a single row in a table, where each value (or field) has a distinctive key, the column name.

The easiest way to create a record is by using the Record Initialization operator, the opening and closing square brackets: [ ]. A simple example of a record could be:

[ Name = "Emily Johnson", Age = 34, Position = "Manager" ]

In this example, Name, Age, and Position are the keys (field names), while “Emily Johnson”, 34, and “Manager” are their corresponding values. The record separates the three key-value pairs with a comma and encloses the statement with square brackets.

Avoiding a Common Error

Records require a unique identifier for each key (field name). A repetition of a key automatically throws an error. Take a look at the example below:

// Returns an Error: The key 'Quantity' is defined more than once
[ Fruit = "Apple", Quantity = 15, Quantity = 20 ] 

This record contains the field name ‘Quantity’ twice and returns the error: “The key ‘Quantity’ is defined more than once”.

Nesting Values

Records can include all kinds of values, including other structured values. That means your record can hold another record or list and any primitive value.

[ 
  Item =             "Apple", 
  Details =          [ Type     = "Fruit", Season = "Fall" ], 
  NutritionalValue = [ Calories = 52,      Sugar  = "14g"  ] 
]

This expression returns the following record:

The output records has three fields and three values.

It only requires you to specify the pairs of keys and values within square brackets, as shown above. But there are more ways to create records.

Creating Records

There are different ways to create records. Whereas we can create one manually, we can also use functions or refer to objects. The following section will give you a brief overview of the different approaches there are to creating records.

Using the Record Initializer

You have just seen the first method to create a record. Provide pairs of field names and field values, comma separated within square brackets, known as the record initializer operator:

[ Fruit = "Apple", Quantity = 15, Price = 1.20 ]

In this snippet, Fruit, Quantity, and Price are the keys (field names), while “Apple”, 15, and 1.20 are the values they hold.

Using Functions

Next to creating records manually, you can also make use of functions to form your record. There are two main functions that support this, being Record.FromList and Record.FromTable. Let’s look at a few examples.

Using Record.FromList

The first obvious function that creates a record is the Record.FromList function. This function has two arguments:

• List of field values: containing the field values for your record.
• Field names: you can either provide a list of text values or a fully specified record type.

For instance, here’s how that could work:

Record.FromList(  
  {"Apple", 15, 1.20 },  
  { "Fruit", "Quantity", "Price" }  
)

Using Record.FromTable

Another useful alternative is the Record.FromTable function. The function takes a table as input and transforms the table into a record. Each record in this list represents a row in the table.

For example, the following table would work:

#table (
  { "Name", "Value"  }, 
  { { "Fruit",    "Apple"  }, 
    { "Quantity", 15       }, 
    { "Price",    1.20     } }
)

If you save the above table in a step called myTable you can turn it into a record by using:

 Record.FromTable ( myTable )

Refer to a Table Row

You can also acquire a record from a table by referencing a specific row within that table. This process is commonly known as item selection. For instance, if there is a table named Source, you can obtain the first row of this table as a record with the expression:

Source{0}

Here, 0 is the index of the first row in the table.

In another scenario, while using the Table.AddColumn function, you can reference a table row by using an underscore (_). Every cell in the newly added column then holds a record containing the current row’s values.

Table.AddColumn( Source, "NewColumn", each _ )

In this code snippet, each _ is a shorthand for a function that takes the current row as its input and returns it as a record in the new column. This underscore symbol (_) signifies the current row being processed.

These methods provide a variety of ways to turn part of a table into a record, allowing you to work with individual rows as structured records.

Field Access and Records

Records are considered a structured value to store your data. You can access fields within records, with two concepts known as Field Selection and Record Projection.

Field Selection

Field Selection allows you to access data within a record. It is the act of pinpointing a specific field by its key (field name), thereby retrieving the value it holds.

The syntax to do that is relatively easy. You specify the key of the desired field within square brackets following the record. Below you can find an example showing this concept:

// Output: "Apple"
[Fruit = "Apple", Quantity = 15, Price = 1.20][Fruit] 

This snippet returns a record with three fields: Fruit, Quantity, and Price. The field selection using [Fruit] retrieves the value "Apple" from the key Fruit.

An alternative method to access a field is by using the Record.Field function. This function takes two arguments: the record and the field name as a string. Here’s a simple example:

// Output: "Apple"
let
  recordExample = [ Fruit = "Apple", Quantity = 15, Price = 1.20 ],
  fruitName = Record.Field( recordExample, "Fruit" ) 
in
  fruitName

This is an easy way to return the value of a specified field.

Let’s say we retrieve the field ‘Size’, which does not exist in the record, as shown below:

// Output: "Apple"
[Fruit = "Apple", Quantity = 15, Price = 1.20][Size] 

This will return the below error:

To prevent this error message, you can add the optional field selection operator (?). With that change, the expression instead returns null for missing field names.

// Output: null
[Fruit = "Apple", Quantity = 15, Price = 1.20][Size]?

You can achieve something similar with the Record.FieldOrDefault function:

// Output: null
Record.FieldOrDefault( 
  [Fruit = "Apple", Quantity = 15, Price = 1.20],
  "Size",
  null
)

Because both methods yield the same result, you’re free to pick the one you like. However, Field Selection is often favored for its more concise syntax.

Record Projection

Record Projection builds onto the concept of Field Selection. It allows for the access of multiple fields from a record, creating a new record with only the specified fields. Record projection ‘glues together’ multiple field selection operations by enclosing them within square brackets.

Here’s an illustration:

// Output: [ Fruit = "Apple", Price = 1.20 ]
[ Fruit = "Apple", Quantity = 15, Price = 1.20 ][[Fruit], [Price]] 

In this scenario, [[Fruit], [Price]] projects a new record with only the Fruit and Price fields from the original record. The outcome is a new record with the projected fields.

If one of the keys (field names) you want to return does not exist, Power Query throws an error.

When you know your records might miss fields, you can incorporate the optional record projection operator as follows:

// Output: [ Fruit = "Apple", Color = null ]
[ Fruit = "Apple", Quantity = 15, Price = 1.20 ][[Fruit], [Color]]?

Alternatively, Record.SelectFields function can be used for a more explicit way to perform record projection. You could then use the optional MissingFieldType to provide options to handle missing fields:

// Output: [Fruit = "Apple", Color = null]
Record.SelectFields (
  [ Fruit = "Apple", Quantity = 15, Price = 1.20 ], 
  { "Fruit", "Color" }, 
  MissingField.UseNull
)

Both Field Selection and Record Projection are useful when working with records. They let you easily access the desired fields in a concise and easy way.

Record Operators

Records support a range of operators. You can use these to compare and manipulate records.

Equal

The first operator supported by records is the equal (=) operator. Records are considered equal when they:

• have an identical number of field names. 
• have similar field names. 
• have identical values in their corresponding field names.  

For example, the following statements both return true.

// Returns true
[ Fruit = "Apple", Quantity = 15 ] =  [ Fruit = "Apple", Quantity = 15 ] 

[ Fruit = "Apple", Quantity = 15 ] =  [ Quantity = 15, Fruit = "Apple"] 

It shows that you can easily move around the order of the field values. But when the field names or field values differ, they are considered different. For example:

// Returns false
[ Fruit = "Apple", Quantity = 15 ] =  [ Fruit = "Apple", Number = 15 ] 

[ Fruit = "Apple", Quantity = 10 ] =  [ Fruit = "Apple", Quantity = 15 ] 

In the first comparison, the field names differ, whereas in the second comparison, the field value of the Quantity field is different.

Not Equal

The not equal (<>) operator tests whether two records are different. It’s the exact opposite of the equal operator and uses comparable rules for testing the equality of the records.

[ Fruit = "Apple" ] <>  [ Fruit = "Banana" ] // Output: true

Concatenate

You can easily combine records using the concatenation operator (&). For instance:

// Returns: [ Fruit = "Apple", Quantity = 15, Size = "XL" ] 
[ Fruit = "Apple", Quantity = 15 ] &  [ Size = "XL" ] 

Combining duplicate field names in a record causes the value of the left record to be overwritten by the one on the right.

// Returns: [ Fruit = "Apple", Quantity = 10 ] 
[ Fruit = "Apple", Quantity = 15 ] &  [ Quantity = 10 ] 

The above expression is identical to using Record.Combine:

Record.Combine( { [ Fruit = "Apple", Quantity = 15 ], [ Quantity = 10 ] } )

As you can tell, knowing how to apply operators can make your code look shorter compared to using functions.

Data Types and Records

A record is an organized set of data containing one or more unique values. If you create a record without specifying a data type, Power Query provides the type ‘any’ by default.

If you then expand this record column, new columns will appear. These new columns will also be labelled with the default ‘any’ data type.

To assign a particular data type to a record, you can use a record type. This provides a framework that outlines the data type for each field in the record. The way you write this is similar to creating a record, but with one key difference: you indicate the data type instead of the field value.

When you expand a column of records that have specific data types, the new columns that appear will maintain these assigned data types.

Use-cases for Records

The use cases for records are numerous. From creating columns in bulk, to storing nested if-statements or using variables. Here are two examples of how to use them in your work.

Using Records for Variables

Records in Power Query can serve as a convenient structure for variables. If you’re familiar with DAX, you know that variables start with ‘VAR’ and end with ‘RETURN.’ Similarly, the Advanced Editor uses the “let..in” construct to define variables. Essentially, this construct is a record. And that’s what we will look at right now.

Why use variables? Three key reasons:

1. Less code repetition and potentially more efficiency when you store a result in a variable.
2. Improved readability when you break down complex statements.
3. Easier troubleshooting, as variables allow you to output intermediate calculations.

Here’s an example of how you can use a record for variables.

// Output: "My Name is "John"
[
  myList  = { "John", 30, "Engineer" }, 
  nameValue = myList{0}, 
  sentence  = "My Name is " & nameValue
][sentence]

In this example, a list called myList stores a person’s details. The element at index 0, “John,” is retrieved and saved in the nameValue variable. A string is then created by appending nameValue to the phrase “My Name is ” and stored in the sentence variable. The output is specifically set to show only the sentence variable’s value and not the entire record.

Yet whenever you need to debug your values, you can simply remove the [sentence] output. The output will then instead return the entire record allowing you to step-by-step check at which variable the result is wrong.

Provide Options to Functions

Another utility of records is to provide options for functions in Power Query. There are many functions that support input parameters to change the output. You are often required to provide these options within a record.

Let’s take the Text.Format function as an example. This function allows you to create a formatted text string using a template and replacement values. The function uses a record value to store these replacement values.

// Output: "The Gorilla wants a Banana"
Text.Format( 
   "The #[Subject] wants a #[Food].", 
   [ Subject = "Gorilla", Food = "Banana" ] 
)

In this example, the record [ Subject = "Gorilla", Food = "Banana" ] is passed as an argument to Text.Format. The function then replaces the placeholders #[Subject] and #[Food] with “Gorilla” and “Banana,” respectively.

Conclusion

In this article, you’ve gained a comprehensive understanding of records in Power Query. We’ve explored how to create records using various methods, from simple record initializers to more advanced functions like Record.FromList and Record.FromTable. You’ve also learned about different operators that enable record comparison and concatenation.

Additionally, we’ve discussed methods to prevent common errors, such as making your field selection optional. Finally, you’ve learned the importance of data types in records, providing you with the tools to work with them more effectively.

With this knowledge, you’re well-equipped to utilize records more effectively. If you enjoyed this article, you may also enjoy reading this guide to lists in Power Query. Happy querying!