Parameters and Functions in Power BI / Power Query – Main Concepts

image
  • Facebook
  • Twitter
  • LinkedIn

Power Query or the Power BI Get Data Experience uses a functional language called M to perform its Data Preparation or Data Transformation processes.

You can read this article to get to know more about Power Query and the M language, but in short, Power Query is the interface that assists you, through buttons and dialogs, to create the M code for you.

Now, where would I see a function, an argument or a parameter in Power Query ?…and what are they? Let’s have a quick example to see them in real life.

Imagine that we have a table like the following:

Header
1
2
3

Now let’s go ahead and load that to Power Query and simply do a Keep Rows –> Keep Top Rows

You’ll notice that we get a new window called Keep Top Rows where we can input the value for the number of rows that we want to keep.

Check what the formula bar says:

By clicking that Keep Top Rows button, Power Query has automatically created a new step and used the correct function for us.

It is using a function called the Table.FirstN which takes 2 parameters.

Function parameters are the names listed in the function definition

You can read the full documentation of the Table.FirstN function here. The function parameters are:

  • Table – the table to check
  • optional countOrCondition – Depending on the type, more than one row will be returned.

Another way to get the full documentation of a function is to simply type it in the formula bar and hit enter:

Function arguments are the real values passed to (and received by) the function

Going back to what we see in the formula bar and the formula that was created for us, we notice that some arguments were passed to the function:

We know that the first parameter of the function requires a table and we see the name of our previous step, which is a table, passed to this parameter as the first argument.

The second parameter requires a count or a condition, and in our case we simply used the number 2, so we want the top 2 rows from this table.

This number 2 is our second argument.

User defined Parameters / Variables

In the pictures where you see the Keep Top Rows window you’ll notice that there’s a drop-down and inside that drop-down there’s an option for a Parameter.

See, back in 2016, the Power Query team implemented a way for users to define their own parameters that could be passed to any function/s of their choice.

in order to define your own parameter in Power Query / Power BI, you need to follow the following steps:

  1. Click on the Manage Parameters button
  2. Now that the Parameters window is loaded, click on Add New Parameter
  3. Define the Parameter as shown in the image above

Once you hit OK, you’ll notice that the result will look like this:

and you can now use that Parameter wherever you want, but it is more commonly passed as an argument to functions.

Let’s do a quick example with our previous query where we can simply replace the 2 with the parameter:

You can read more about how to re-configure a step in Power BI / Power Query from this blog post.

The main benefit of Parameters in Power BI / Power Query

There are multiple benefits of Parameters in Power BI / Power Query, but the main point is how you’re essentially creating a parameter and how you change its argument (value) so easily.

Of course, a Parameter can even help you create a User Defined Function and that’s something that we’ll see in the next blog post in this series, but at the very least you can have an easy way to change the argument of your parameter as described in this blog post and showcased in the following video:

Summary and next steps

  • A function is a mapping from a set of input values to a single output value. A function is written by first naming the function parameters, and then providing an expression to compute the result of the function.
  • Function parameters are the names listed in the function definition
  • Function arguments are the real values passed to (and received by) the function

You need to have these concepts clear before into a creating your own function or trying to read and edit M code. These are the core elements of the M language.

In the next posts in this series I’ll go over:

  • User Defined Functions in Power Query / Power BI
  • Parameter Tables

Got any suggestions or feedback for future blog posts? leave them in the comments section down below.