Creating & Invoking Custom Function in Power Query

Microsoft’s Power BI is a data and analytics reporting tool that lets you connect to multiple data sources. Once connected to a data source, raw data sets can be converted to dashboards and presented to the team, customer, anyone at any time.
In this blog, we will get introduced to the custom function feature available in Power Query, a data transformation component of Power BI Desktop.

Let’s Dive

Launch the Power BI Desktop and get the data in Power BI Desktop to work on. (Here, we are using sample financial data in .xlsx format).

We need to use our own custom functions when complex calculations that we want to perform with our data are not possible with the available standard, statistics, and scientific transformation features.
When we want to use our own function, first, we need to create that function.

Let’s have a look to create and use a simple custom function in Power BI Query Editor.

Step 1: After importing the data in Power BI Desktop, go to Power BI Query editor.

Step 2: In Power Query, we can find the Queries section on our left-hand side. In the blank area of the Queries section, right-click and select new query and then blank query.

After selecting the new blank query, we will be writing our custom function inside the formula bar.
The syntax for the custom function is as follows –
= (Variable as Data Type, Variable as Data Type) => (Output Expression)

1. Our custom function will always start with the Equals to (=) operator.
2. Variable like X, Y, Z, x, y, z, and its Data type like number must be declared inside the parentheses (). Each variable followed by its data type needs to be separated using comma (, ) from other declared variables.
3. Symbol => refers to the end of variable declaration, and after => symbol, we need to write our Output Expression inside parentheses ().

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Let’s understand it by creating a simple custom function to get a product of two numbers.

Step 3: In the formula bar, we will write the query as below and then hit Enter.
= ( X as number , Y as number ) => ( X * Y )

Note: M-language is case sensitive, and therefore, the variable used must have a uniform case.

Our custom function to get the product of two numbers is ready. We can test the function by passing values to variables in the function.
To test the function, I am passing the value of X as 18 and the value of Y as 20 and clicking Invoke. The expected output is 360 as per our output expression.

Here, we can observe the desired output, but the output is recorded as a new query. We can delete this output query.

Now, we will see how we can invoke the custom function for our desired query in Power BI Query editor.

Step 4: Select the desired query in which you like to Invoke the custom function and then go to Add columns tab.

Step 5: The last thing we need to do is select the Invoke custom function feature to call the custom function.

We can Invoke custom functions multiple times for different or the same queries.

Once we click OK, we can find the result in the same query table in a new column in which we invoked our custom function.

In this post, we have seen the procedure to create and invoke custom functions in Power BI Query Editor.
Hope you enjoyed the post.