Parameters and Custom Functions

The M language is a functional language containing hundreds of functions suited for a great number of tasks. At first, the standard function library will likely meet most of your transformation needs. However, once you run into more challenging situations with the M language, you will find that being able to write your own custom functions opens up new possibilities; it can greatly simplify the data transformation process and make it so you can easily repeat complex logic. This is especially true when you leverage a feature like creating a function, which allows you to transform existing queries into dynamic, reusable functions. The biggest benefit here is that you can set up your logic once and apply it with ease everywhere else. If you need to modify your logic later, simply update your function. This change will automatically propagate to all queries using that function.

Accordingly, this chapter covers the following:

• Parameters
...

Parameters

Parameters in Power Query play an important role in making queries dynamic. Think of them as variables that you can tweak to change the behavior of your queries. As you go through this chapter, you’ll discover how parameters are a useful building block for storing and managing values. We will delve into what they are, how you can create one, and look at where and how to use them.

Understanding parameters

Parameters are placeholders that make it easy to adapt and manage your queries. They allow for the input of scalar values like dates, numbers, or text, without the need to hardcode these values directly into the queries. This design enables you to make external modifications to a single parameter, which then automatically propagates updates across multiple queries or steps within a query.

Typical use cases for using parameters provide flexibility by allowing a user to:

• Choose a server and database name. This allows users to switch between development...

Custom functions

Custom functions play an important role in the M language. They allow you to consolidate your logic, making your code modular, reusable, and efficient. Think of a custom function as a tool designed for a specific task.

And while there are hundreds of standard library functions available, sometimes creating your own function is useful. They allow you to create solutions specific to your data challenges that consolidate multiple custom steps or address data transformation issues that built-in functions do not handle.

For instance, consider complex tasks like creating running totals, retrieving the previous row value, or computing an ISO week number. Manually writing the code for these transformations can be complicated and error-prone. Yet, by providing a custom function, you can now achieve the desired outcome with a reusable function call that you only need to set up once. Once created, these functions can easily be reused, making it easier for team members...

Summary

This chapter introduced parameters. Parameters, as you’ve seen, are more than mere placeholders in Power Query. They provide you with flexibility and cater to varied scenarios. As you progress in your Power Query journey, you will find yourself reaching out to parameters more and more to make your queries easier to adjust.

We also took a deep dive into the world of custom functions in Power Query M. They allow you to turn your custom queries into reusable logic. You learned how to transform a query into a function without having to write it yourself. We then covered the ins and outs of invoking functions and highlighted the role of the each expression in simplifying the creation of functions.

We also explored how the syntax varies when calling single- versus multi-argument functions. In more intricate situations, where scope becomes a concern, working with the each keyword does not suffice. For these cases, creating your own custom function is required. Beyond...