Microsoft’s Power BI is a data and analytics reporting tool that lets you connect to the multiple data sources. Once connected to data source, raw data sets can be converted to dashboards and can be presented to the team, customer, anyone at any time.
In this blog, we will get introduced to custom function feature available in Power Query which is 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).

When complex calculations that we want to perform with our data is not possible with the available standard, statistics, and scientific transformation features, we need to use our own custom functions.
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 Queries section to our left-hand side. In 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.
Syntax for custom function is as follows –
= (Variable as Data Type, Variable as Data Type) => (Output Expression)

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

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

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

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

Our custom function to get 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 value of X as 18 and value of Y as 20 and click 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 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: Last thing we need to do is to select the Invoke custom function feature to call the custom function.

We can Invoke custom function multiple times for different or 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.