Working with Nested Structures

Nested structures are very common and refer to the hierarchical organization of data, where a table, record, or list is contained within another. This provides an efficient way to organize relationships and hierarchies and store arrays of values. Common data sources supporting nested structures include relational databases, JSON, and XML. In addition, there is a wide range of M functions that yield structured values. Getting a good grasp on how to handle these structures is crucial; it unlocks a wealth of possibilities.

This chapter covers common functions for working with nested structures in the Power Query M language. These functions allow you to extract specific elements from nested tables, lists, and records, filter and transform nested data, and create new structures based on existing ones. It aims to equip you with the necessary knowledge to apply these transformations in your own workflows. The main topics covered in this chapter are:

...

Transitioning to coding

Throughout the book so far, we have progressively developed an understanding of the Power Query M language. That knowledge is paramount for achieving more advanced data manipulation and transformation tasks. Many common operations can be executed via Power Query’s user interface (UI), enabling users to modify values by interacting with menus and buttons.

However, there are many cases where transforming nested structures cannot be done through the UI and manual coding is required. In this section, we are going to cover some fundamentals, share a trick to getting the most out of the UI, and set you up with the basic skills you need to start coding M.

Getting started

Tables are the primary data structure in Power Query. They offer an intuitive way to represent data, making it easier for the user to understand and work with that data. Moreover, the Power Query editor is specifically tailored to work with tables, providing a wide range of table...

Working with lists

Considering the numerous functions designed to work with lists, their significance is evident. You can think of lists containing primitive values as similar to one-dimensional arrays: a flexible structure that allows for the easy storage and manipulation of data, including adding, removing, and modifying items. This section focuses solely on working with lists; lists that contain other value types, like records or tables, are covered later on in this chapter.

Transforming a list

Every element within a list can be referred to by its positional index – a number that reflects its zero-based position within a list. The first element has a positional index of 0, followed by the second element with an index of 1, and so on. A method called item access provides access to each list element by using this zero-based index position within a set of curly brackets, { }.

List.Transform

List.Transform provides a wealth of possibilities. Much like the name...

Working with records

Records are a structure that allows for the organization of data in fields; each field is a name-value pair. Similar to lists, records can store various data types and offer capabilities for data manipulation, extraction, and modification.

While there is only a modest collection of record functions available in the M language compared to lists or tables, there are functions that accept a record as an argument or return a record as output. This section will cover common aspects of working with records. Again, it’s important to mention that our focus is on working with records, and we will discuss working with mixed data structures later in this chapter.

Transforming records

Every field value within a record can be referred to by its field name – a unique identifier within the record. A method known as field access provides access to each field value by using that name within a set of square brackets, [ ].

The M language includes a...

Working with tables

Tables are structured collections of rows and columns, where each cell can hold data of any type. They are the predominant structure in Power Query, as evidenced by the way the UI has been designed to operate on them. Now it’s time to explore the basics of nested tables and see examples of how to create, access, and manipulate them. Our primary focus is on understanding the nuances of working with a nested table structure.

Here’s our sample dataset. Call this query SurveysData; we will refer to it using that name from now on:

let
    Source = Table.FromRows( List.Zip( { List.Transform( {"1".."5"}, each "Wave "& _ ),
        Table.Group( Table.FromRows(
        {
            {456, 30, "Female", "Intermediate", "High", "Good", "Yes", "Well organized content", #date(2023,3,1), #date(2023,3,10), 5, 8},
            {457, 52, "Male", "Expert...

Working with mixed structures

This section focuses on structured values that contain structures of a different type. Although a wide range of scenarios is possible, we cannot possibly illustrate them all; however, this section should provide enough insight into how to tackle the most common challenges you are likely to encounter.

Lists of tables, lists, or records

As demonstrated earlier, handling a list of items that share a consistent structure and are grouped together can easily be managed and converted to a table. This approach is incredibly powerful and applies equally to working with a single column in a table or converting lists in every table row in a table to reshape the data.

Let’s explore the first scenario, dealing with columns that contain structured values. Imagine a table organized in the following manner:

• Table values
• List of lists, where each inner list represents column values
• List of lists, where each inner list represents...

Summary

In this chapter, we’ve set the stage for you to confidently step beyond the confines of the UI, by introducing a low-code approach to kickstart that journey. This shift in your ability will provide access to a vast number of functions that will enhance your data transformation capabilities further.

We explored working with lists, records, and tables, including when nested inside other structures or when mixed. The aim has been to provide you with practical techniques for extracting, reshaping, and transforming these values, occasionally showcasing multiple methods that achieve the same outcome and thereby highlighting the flexibility of the M language.

Understanding each dataset comes with its unique challenges and requirements. We presented a variety of examples covering a wide range of scenarios. Whether straightforward, like selecting data, or more intricate, the goal has been to inspire and prepare you for a wide range of potential puzzles that might find...