Power Query M Primer (Part 10): Types—List, Record

, , , ,

You might be inclined to skip learning about list and record and jump directly to table. After all, as far as types that hold other values go, you mostly work with table. A table’s data is made up of simple values, so it makes since that you learned about them (parts 6, 7, 8 and 9). However, list and record might feel like types you won’t normally use, so why not skip them?

Turns out, a table behaves in ways like both a list and a record. If you want to leverage all that table offers, learning about these types is foundational. Besides, as you work with M, you just might find that list and record are more useful in their own right than you first thought.

Oh, did I mention that there’s a surprising relationship between let expressions and type record? All the more reason not to skip over it!

Series Index

list

Type list stores exactly what its name implies: a list of values.

{ 1, 2, 5, 10 }
{ "hello", "hi", "good bye" }

As you might expect, a list can be empty:

{ }

Out of the box, a list’s values don’t need to be of the same type.

{ "hello", 1, 6, true, #date(2018, 5, 6) }

(It’s possible to create lists that are constrained to only hold values of a certain type, but that’s an advanced scenario which requires knowledge of M’s type system, so we won’t cover it here.)

Since a list can contain values and a list is itself a value, a list can contain lists which in turn can contain lists and so forth.

{ { "first name", "last name", "children" }, { "Sue", "Smith", { "Sarah", "Sharon" } } }

If you’re interested in an increasing sequential list of whole numbers, .. is a handy shortcut, saving you from needing to literally type out the entire list.

{ 1..5 } // equivalent to { 1, 2, 3, 4, 5 }
{ -2..0 } // equivalent to { -2, -1, 0 }
{1, 6..8, 15 } // equivalent to {1 , 6, 7, 8, 15 }

This shortcut only works to produce lists of increasing values. Attempting to use it to produce a sequence that counts down will leave you with an empty list.

{ 5..1 } // { } -- outputs an empty list

Multiple lists can be concatenated using the combination operator (&).

{ 1, 2 } & {3, 4 } // { 1, 2, 3, 4 }
{ 1, 2 } & { 2, 3} // { 1, 2, 2, 3 }

Two lists are equal if they contain the same values in the same order. Not equals works just as you’d expect (exactly the opposite of equals).

{ 1, 2 } = { 1, 2} // true
{ 1, 2 } = { 1, 2, 3} // false
{ 4, 5 } = { 5, 4 } // false -- same values but different order

{ 2, 4 } = { 2, 4 } // true
{ 2, 4 } <> { 2, 4 } // false

Greater than (>) and less than (<) comparisons are not supported on lists.

Item Access

List items can be accessed using the positional index operator. Simply take a reference to the list of interest and append the index of the desired list item surrounded by curly braces:  SomeList{SomeIndex}.

In M, list indexes (or indices, if you prefer) are 0-based, meaning that the first list item is at index 0, the second item is at index 1, and so forth. So, to access the first item, use an index of zero.

Assuming Values = { 10, 20, 30 }, the following expressions produce the indicated output:

Values{0} // 10 -- value of 1st item in list
Values{1} // 20 -- value of 2nd item in list
Values{2} // 30 -- value of 3rd item in list

If an attempt is made to access an index larger than what’s in the list, an error is returned.

Values{3} // Expression.Error - There weren't enough elements in the enumeration to complete the operation.

If, instead, you’d rather receive a null when a too large index is used, append a ? to the curly braces.

Values{3}? // null

Lazy Evaluation

Lists are evaluated lazily. The below list doesn’t produce an error, even though the value of an item in it is defined as an expression that raises an error. We didn’t asked for the value of that item, so no attempt was made to generates its value. Since the error-raising expression was never invoked, no error was raised.

let
  Data = { 1, 2, error "help", 10, 20 }
in
  List.Count(Data) // 5

All we asked was “how many items are in the list?,” and that’s all M figured out. Whether or not they are all valid is a different question, one we didn’t ask and one M’s mashup engine didn’t try to answer.

Also, when values are needed, M’s laziness means that it only evaluates as many list items as are necessary to produce the requested output. Using Data from the above example, the following expressions do not raise errors. Neither needs the value of index 2, so the error that would be raised if that item’s expression were evaluated isn’t raised.

List.Sum(List.FirstN(Data, 2)) // 3 -- sum up just the first two items
List.Sum(List.LastN(Data, 2)) // 30 -- sum up just the last two items

Library Highlights

As you might expect, the standard library includes a number of methods for working with lists. They cover tasks from counting items to searching for text, from doing math on a list (sum, product, etc.) to transforming a list (e.g. remove items, replace items, reverse, etc.), from generating statistics (e.g. average, max, standard deviation) to testing membership (like “does it contain this value?” or “do all values in the list cause the provided function to return true?”), as well as supporting set operations (union, intersect, difference, zip) and sorting. There’s even a family of functions for generating lists of values of other types (handy, say, if you want a sequential list of datetimes or durations or maybe a list of random numbers, etc.).

record

A record allows a set of named fields to be grouped into a unit.

[ FirstName = "Joe", LastName = "Smith", Birthdate = #date(2010, 1, 2) ]

Technically, a record preserves the order of its fields. However, as we’ll see in a moment, field order isn’t considered when comparing records, so mostly this preservation of field order is a convenience for humans (e.g. fields will be output on screen in the same order you defined them, making it easier for you to visually locate data items of interest).

An empty record contains no fields (no surprise!).

[ ]

Equality is determined by field name and value. Field position is not considered.

[ a = 1, b = 2] = [a = 1, b = 2] // true
[ a = 1, b = 2] = [b = 2, a = 1 ] // true -- same field names and values, even though ordering is different

[ a = 1 ] = [ A = 1 ] // false -- different field names
[ a = 1 ] = [ a = 2 ] // false -- same filed names but different values
[ a = 1 ] <> [ A = 1 ] // true

Records can be merged with the combination operator.

[ a = 1 ] & [ b = 2 ] // [ a = 1, b = 2]

If the same field name is present in both merge inputs, the value associated with the field from the on the right is used.

[ a = 1 ] & [ a = 10 ] // [ a = 10 ]

Field Access

Remember how lists use {index} to access list items? With records, something similar is used—the lookup operator, which consists of the field name inside square brackets: SomeList[SomeField]

If Value = [ Part = 1355, Description = "Widget", Price = 10.29, Internal Cost = 8.50 ] then the following expressions will return the noted values:

Value[Part] // 1355
Value[Description] // "Widget"
Value[Price] // 10.29
Value[Internal Cost] // 8.50

Similar to list, appending a ? to the lookup operator changes its not-found behavior from an error to returning null (technically, this is called “performing an optional field selection”).

Value[NonExistentField]  // error - Expression.Error: The filed 'NonExistentField' of the record wasn’t found.
Value[NonExistentField]? // null

Within a record, the expression for a field value can reference other fields.

[
  FirstName = "Sarah",
  LastName = "Smith",
  FullName = FirstName & " " & LastName
]

A field’s expression can even reference itself if its name is proceeded by the scoping operator (@).

“Why would a field want to reference itself?” you might ask. This behavior may not seem intuitive in the context of a field containing a data value. However, the ability to self-reference comes in handy when the value is a function because it allows the function to be recursive.

[
  AddOne = (x) => if x > 0 then 1 + @AddOne(x - 1) else 0,
 AddOneThreeTimes = AddOne(3)
][AddOneThreeTimes] // 3

Projection

In addition to square brackets being used to select record fields, they can also be used to perform record projection—that is, reshaping a record to contain fewer fields. Below are a couple examples (assume that Source = [ FieldA = 10, FieldB = 20, FieldC = 30 ]):

Source[[FieldA], [FieldB]] //  [ FieldA = 10, FieldB = 20 ] -- FieldC was removed
Source[[FieldC]] // [ FieldC = 30 ] -- all fields except C were removed

Similar to when [] are used for field selection, with projection, referencing a non-existent field causes an error. However, if a ? is appended, any non-existent fields referenced by the projection expression will be added to the output with their values set to null.

Source[[FieldA], [FieldD]] // error - Expression.Error - The field 'FieldD' of the record wasn't found.
Source[[FieldA], [FieldD]]? // [ FieldA = 10, FieldD = null]

Relaxed Quoting Rules

Within the square bracket syntax of record initialization, selection and projection, field names have relaxed quoting rules. Normally, to start an identifier with a number, to use a M language keyword as an identifier or to include whitespace in the middle of an identifier, the identifier must be quoted. However, in these situations quoting is optional for field name identifiers inside square brackets.

Outside of square brackets, the identifier Street Address needs quoting because it contains a space and try needs quoting because it’s a keyword. Below, inside the square brackets, quoting these identifiers is optional:

[#"try" = true, #"Street Address" = "123 Main St."]
[try = true, Street Address = "123 Main St."] // identical in effect to the preceding

SomeRecord[#"Street Address"]
SomeRecord[Street Address] // identical in effect to the preceding

SomeRecord[#"try"]
SomeRecord[try] // identical in effect to the preceding

Note, however, that M assumes whitespace occurring at the start or end of an unquoted field name can be ignored and so excludes it from the field name. If, for some reason, you want leading or trailing whitespace to be a part of a field name, you’ll need to quote it.

SomeRecord[ Street Address ] // accesses field "Street Address"
SomeRecord[#" Street Address "] // accesses field " Street Address "

Lazy Evaluation & Value Fixing

Like list, record is lazy. If a value isn’t needed, it isn’t evaluated.

[ Price = 10, Quantity = error "help"][Price] // 10

Above, since Quantity wasn’t needed, its value wasn’t evaluated. Since its value wasn’t evaluated, no error was raised.

When a field is evaluated for the first time, the resulting value or error is fixed as the value for that field. The field’s expression is only executed one. Its output is then cached. The cached value or error is returned each subsequent time the field is accessed.

[ Price = GetValueFromRemoteServer() ]

Imagine that the first time Price is accessed, the remote server returns 10. Later on while your mashup is still running, the record’s Price field is accessed again. Perhaps by this point in time, invoking GetValueFromRemoteServer() would return 11. However, that method is not re-executed. Instead, the value cached when the field was first accessed (10) is returned.

If, instead, when Price was first accessed, GetValueFromRemoteServer() raised an error due to a temporary communications glitch, that same error will be re-raised each subsequent time Price is accessed, even if by the time the subsequent access occurs, the glitch is resolved and GetValueFromRemoteServer() would return a non-error value if it were invoked.

This value fixing (or caching) provides consistency. Thanks to it, you know that a field’s value will always be the same throughout your mashup’s execution.

Value caching is not shared across record instances, even if the records have identical fields and field value expressions. If your code causes the record [ Price = GetValueFromRemoteServer() ] to be generated twice and Price is accessed on both instances, each will separately invoke GetValueFromRemoteServer() once and cache the value returned. If the value returned is different between the two invocations, the two records will have different values for Price.

If the record you are working with is assigned to a variable, each time you access that variable, you’ll access the same record instance. However, if instead you access a record multiple times by invoking an expression that retrieves it from an external source (e.g. a database or web service), each retrieval may technically return a different record instance. If it’s important to be sure that you are always working with the same record instance, retrieve it once then save it to a variable or, in the case of a list of records, buffer the list.

Library Highlights

In the standard library, you’ll find several functions for working with records, including methods to add, rename, reorder and remove fields as well as to transform field values. There is also a method returning a list of the record’s field names (with field order preserved) and a similar method returning field values.

Dynamic Operations

Above, we used the lookup operator to access field values by hard-coded names. What if, instead, we wanted to use programmatic logic to choose the field to access? The following doesn’t work because field names inside square brackets must be strings; variable references aren’t allowed.

let
  Item = [Name = "Widget", Wholesale Price = 5, Retail Price = 10],
  PriceToUse = "Wholesale Price"
in
  Item[PriceToUse] // doesn’t work--doesn’t return the value of field "Wholesale Price"

To solve this dilemma, the standard library comes to the rescue. Record.Field is the dynamic equivalent of the lookup operator. Record.FieldOrDefault works like a dynamic lookup operator followed by a question mark, with the added bonus of optionally allowing you to specify the value to be returned if the field name doesn’t exist

Record.Field(Item, PriceToUse) // returns 5

If, instead, PriceToUse is set to “Sale Price” (a value which doesn’t correspond with a fieldname), then:

Record.Field(Item, PriceToUse) // error - Expression.Error: The field 'Sale Price' of the record wasn’t found.
Record.FieldOrDefault(Item, PriceToUse) // returns null
Record.FieldOrDefault(Item, PriceToUse, 0) // returns 0

Similarly, if we want to dynamically perform projection, Record.SelectFields is our go-to. There are also standard library functions to remove fields (instead of projecting by listing the fields desired, specify the undesired fields and a new record containing all of the other fields will be returned) and to reorder fields (handy in those few cases where field order matters).

let Sugar

Ready for a surprise? A let expression is, in essence, syntactic sugar for an implicit record expression.

let
  A = 1,
  B = 2,
  Result = A + B
in
  Result

Is equivalent to:

[
  A = 1,
  B = 2,
  Result = A + B
][Result]

It might be good to pause and ponder this for a moment. This fact means that what we know about how records work also applies to let expressions and vice versa.

For example, we know that a record field’s value is computed on first access then cached. Since let is in essence a record expression, this same immutability rule applies to it: a let variable’s expression will be evaluated on first access then its value will be cached. However, for let expressions, we know there’s one exception to immutability, which occurs when streaming comes into play. This same exception must also apply to records…it must because let and record share the same behavior.

Next Time

Did you notice that record looks like it would be great for holding a row of data? If you wanted to store multiple records, each representing a row of data, in a single variable, you could put those records in a list. Hm…sounds like we’re getting close to what a table is!

Good news: tables are up next time! We’ll learn about how they share behavior with both list and record while at the same time offering more than we’d get if we tried to simulate a table using a list of records.

Looking forward to it! Until then, happy M coding!

One thought on “Power Query M Primer (Part 10): Types—List, Record

  1. Daniel Herce

    Hi Ben
    You’re doing a great job in your articles explaining the “M” language fundamentals. Waiting for the next one!
    Daniel

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *