Iteration and Recursion

Iteration and recursion are fundamental concepts in programming that allow for the repetitive execution of code. In the context of Power Query M, these techniques greatly enhance your data transformation and manipulation abilities. Whether you’re looking to apply a function across a list of values, accumulate results, or even refer to previous steps in a sequence, understanding iteration and recursion makes a big difference in these situations.

This chapter delves into key functions and operators that enable iteration and recursion in the Power Query M language. You’ll learn how to loop through lists with List.Transform, perform a function on values with List.Accumulate, generate lists conditionally with List.Generate, and implement true recursion using the @ scoping operator.

The aim is to provide you with the skills needed to incorporate these techniques into your own data workflows. The main topics covered are:

• Iteration...

Introduction to iteration

Iteration is a core concept in programming, important for performing repetitive actions on data. In Power Query, iteration often happens over rows in a table or items in a list.

Suppose you want to square a list of 1,000 numbers. Doing this without iteration would require writing 1,000 separate lines of code, which is both time-consuming and error-prone. With iteration, the same task can be completed with just a few lines of code.

Traditional languages like Python or Java use for and while loops for such tasks. However, the Power Query M language takes a different approach to iteration. It performs transformations on its underlying data structures by design.

For example, when you use Table.AddColumn to create a new column, Power Query automatically applies the specified operation to each row in the table, similar to how a for loop would iterate over each element in a list. The M language automatically does the iteration for you using its standard...

Recursion

Recursion is a programming term that sounds more complex than it really is. Simply put, recursion happens when a function calls itself within its own definition. Think of it as a loop, but instead of using a typical for or while loop, the function uses itself to perform an operation multiple times.

Recursion is helpful for addressing problems where you need to repeat a task but the number of repetitions isn’t known beforehand. This operation requires an ending condition so that the iterations stop when they reach a specific condition, preventing infinite repetitions.

The concept of recursion has also been compared to the concept of Russian dolls. Imagine opening a Russian doll to find another one inside, and another inside that, and so on. This process continues until you reach the smallest doll that can’t be opened further. Each step is like a function calling itself, eventually reaching a point where it can no longer do so.

Why is recursion important...

Summary

In this chapter, we explored two important concepts: iteration and recursion. Beginning with iteration, we examined the role of the List.Transform and List.Accumulate functions. Both perform their tasks on a list with a fixed number of items, like a for loop. While List.Transform serves its purpose for straightforward operations, List.Accumulate is also helpful for scenarios requiring access to the result of earlier iterations.

We then delved into the List.Generate function, which allows you to dynamically create lists based on custom conditions and logic, similar to a while loop. The function has good performance, provides an easy way to debug its results, and is the preferred choice over traditional recursion in most use cases.

Lastly, we tackled the concept of true recursion, facilitated in M through the @ scoping operator. Although recursion comes with a cost in terms of memory and speed, you can consider using it for tasks involving a limited number of iterations...