This tutorial will discuss the Function Query feature in the Query Editor. You’ll learn how to use and maximize function queries to get the results and data you desire. You’ll also understand how they work with operators to generate specific outcomes.
Creating A Function Query
Power Query allows users to define custom functions which map a set of arguments into a single value. To demonstrate, right-click on the space in the Query Pane. Then, click New Query and select Blank Query.
Next, open the Advanced Editor window and delete all its contents. If you want to construct a custom function, you have to start with a set of parentheses. Then, define a comma-separated list of parameters inside those parentheses. After that, input the go-to sign, which is the combination of the equal and greater than sign, followed by the function body.
In this example, the parameters are a and b, and the function body is a + b. Name the query Add2Values.
This is what the Function Query looks like.
Beside the query name in the Query Pane, you can see the fx icon which indicates that it is a function query.
To invoke the function, enter a value for each parameter and press Invoke.
Pressing Invoke will create a new query called Invoked Function, which contains the result of the set parameters. In the formula bar, you’ll also see that it references the function query by name and assigns the values of the parameters.
To add values from different columns, you may also use the same Function Query. Create a new query and open the Advanced Editor window. Next, input the following code to create a small table.
Invoking A Custom Function Query
To invoke a custom function on each row of the table, you can go to the Add Column tab and select Invoke Custom Function.
Another way is to click the mini table icon in the upper left corner of the preview and select Invoke Custom Function.
If you click the Invoke Custom Function, a dialogue box will appear. Input AddValues as column name and select Add2Values as Function Query. Then, set the values for each parameter.
In this example, the values are in Columns 1 and 2.
You can see that a new column has been added to the table. The values inside the column are the sum of the row values of Columns 1 and 2.
Removing An Argument
If you remove one of the arguments inside the formula, the values inside the new column will yield an Error. In this example, Column2 is removed from the formula.
If you click the space beside the Error value, you’ll see the error message below the preview pane.
The custom function has a required set of parameters which allows us to create optional function parameters.
For the next step, go back to the Add2Values Function Query and open the Advanced Editor window. Then, add the keyword optional inside the parentheses and click Done.
If you go back to the SumExample Table Query, you’ll see that the Error values in the last column turn to null values. Applying the operator to values that include a null will always return a null.
Another thing to be aware of is that Function Query accepts arguments of any type. This could potentially cause problems because you could pass a text value and raise another error. The addition operator can’t be applied to operands of that data type.
In the Advanced Editor window, you can type functions by adding the keyword as. Aside from typing the parameters, you can also assign a return type to the function after the parentheses.
Adding An Argument
Adding too many arguments will also get error values. If you input Columns 2 and 3 in the formula bar, the last column will show error values.
Again, if you click the space beside the error value, you’ll see the error message below the preview pane.
There is an M function that helps deal with a situation like this. First, create a new blank query and input the Function.From function in the formula bar. You’ll then see documentation of the function.
To demonstrate, duplicate the Add2Values Function Query and open the Advanced Editor window. Then, input Function.From at the beginning of the syntax.
For the first argument, input function as the function type. For the second argument, input List.Sum to sum up the values and return a list. Once done, click Okay and rename the query to AddValues.
Next, go back to the SumExample Table Query and change the Function Query to AddValues. You’ll see that the AddValues column now has the sum of each row values of the column.
Even though two parameters were only declared in the function type, you can invoke the function with as many arguments as you want. This is because all arguments are merged into a single list before passing it to the function.
How you name your parameters doesn’t matter.
If you’re writing a custom function within the Function.From and you need to reference an item, you have to use the positional index operator to access the item in the list.
Understanding Unary Functions
Unary functions are functions that you see all the time. Many of the standard library functions take functions as arguments and those parameter functions are often unary. It means that the function takes just one single argument.
As an example, add a filter example query by creating a new blank query. Next, open the Advanced Editor window and input the following syntax.
Once done, you’ll see a table with CustomerID and Name columns in the preview pane. Name the query FilterExample.
Writing Codes In A Function Query
Instead of using the user interface to generate the code, you can just write the code yourself. If you want to select rows where the customer ID is greater than 2, click fx beside the formula bar to manually enter a step. Power Query automatically returns the variable name of the last step in the Applied Steps pane.
Next, input the Table.SelectRows function and its arguments in the formula bar. The first and second arguments must be a table and a condition as a function, respectively. In this example, the first argument is the ChType and the second argument is a custom function that brings out the customer ID greater than 2.
Another way is to use the each keyword, which is a shorthand for a unary function. It takes a single nameless variable as an argument and is represented by the underscore ( _ ). To demonstrate, open the Advanced Editor window and change the custom function.
Once you press Done, you can see that it generates the same results.
To improve the readability of the formula, you can omit the underscore when accessing fields or columns.
If you go back to the Advanced Editor window and remove the underscore in the custom function, it will still return the same results.
All the expressions are equal to one another. But from a readability and writing standpoint, the last version is definitely easier to understand. When creating this step through the user interface, the M engine uses the shorthand notation.
***** Related Links *****
Extended Date Table Power Query M Function
M Function For Date Table – How To Add A Parameter In The Query Editor
Creating A Custom Text Cleaning Function In Power Query
Conclusion
A Function Query utilizes and maximizes functions to obtain data. They help bring out or gather specific information from a table or source to provide results. You can use these functions to effectively create a data report and improve your data development skills.
Melissa
