Structured Values

In this chapter, we focus on an important concept in the Power Query M language: structured values. Whereas we introduced both values and data types in Chapters 4 and 5, structured values are more complex and require additional attention. Unlike simple or primitive values, they can include multiple other values within them. This complexity allows them to hold a range of primitive values, or even other structured values, opening up a wide range of possibilities for data manipulation and analysis.

The importance of structured values within the M language is such that we’re dedicating an entire chapter to them. The constructs you will learn not only contribute to more efficient and effective code, but also form the backbone of many operations within the M language.

This chapter covers the following topics:

• Introducing structured values
• Lists
• Records
• Tables

In each section, we will provide a clear explanation of the...

Introducing structured values

As we delve further into the M language, we arrive at one of the most important concepts: structured values. These values form the foundation of data shaping and manipulation in Power Query M. But what exactly do we mean when we say structured values? You can think of them as a container or package that can contain one or more values. These containers are organized in a way that allows us to perform transformations on them. As a developer, getting a thorough understanding of how to use these values allows you to perform challenging data transformations more easily.

Structured values allow us to deal with complexity by splitting data into manageable chunks. Each structured value – be it a list, record, table, or function – has its unique characteristics and applications, and understanding when to use each helps in writing effective code.

As we go through this chapter, you might find some concepts slightly more challenging than others...

Lists

Beginning our exploration of structured values, we first encounter lists. So, what is a list?

Introduction to lists

A list contains a sequence of comma-separated values of any type. That includes primitive values (like text, numbers, dates, or times) and structured values (lists, records, or tables).

So why should lists interest you to begin with? You will find that lists are used extensively throughout Power Query. For example, selecting a column returns its values in the form of a list. Similarly, when functions take multiple values as input (like List.Count) or return multiple values as output (like Table.ColumnNames), you will often find that these functions involve lists. Let’s have a look at how you can create a list.

Creating a list is a straightforward process. You can define a list value by enclosing values in curly brackets { }, formally known as list initialization. Each value within the list is separated by a comma.

For instance, you can...

Records

Next on our journey through structured values in Power Query’s M language, we encounter records.

Introduction to records

A record is a named list of values. While lists are a simple, ordered collection of values, records associate a field name or key to each value. This leads to a more complex, yet organized data structure.

You can think of a record as a single row in a table, where each field in the row has a unique column name and a value. Just like lists, records can contain any type of value, be it primitive (such as text, numbers, or dates), or structured (like lists, records, or tables).

You can create a record with the record initialization operator. This involves specifying pairs of keys and values within square brackets [], also known as record initialization. What’s important here is to remember that:

• Every record is surrounded by square brackets.
• A key (the field name) is followed by an equal sign (=). Records allow...

Tables

Now that we’ve taken a deep dive into lists and records, it’s time to extend our understanding to table values. As with lists and records, tables hold an important place in the M language due to their role in structuring and organizing data. So, what is a table?

Introduction to tables

At its most basic, a table is a structured value that arranges data in rows and columns. You could also say that a table is a list of records where each record represents a row of the table. Because of its two-dimensional form, it is easy to preview your data in a table and you will find that most queries that are loaded into Power BI or Excel have a table value as output. So how do you create a table?

Creating a table in Power Query’s M language is often done by calling an accessing data function as described in Chapter 3. For instance, when you import an Excel or CSV file, Power Query automatically creates a table for you.

However, to illustrate the characteristics...

Summary

In this chapter, we explored lists, records, and tables, showing how important they are when you’re using the M language.

We learned that structured values serve as containers holding one or more primitive or structured values. Learning about them is useful in many different areas. For instance, lists and records are often used to provide multiple items in function arguments and they help in simplifying your code by using them. Record structures are also great structures in which you can create variables.

We also investigated how to access items from the different structured values through selection and projection, enabling you to easily extract values. You will find that these skills help you understand code created by the user interface, but also help you create shorter code yourself.

We then looked at the creation of these values and how operators work on them. You also learned about complex data types, highlighting their importance and how to assign...