Custom function in Power BI

Custom function in Power BI allows you to use a same piece of code repeatedly multiple times.

Sometime, you may get a situation where you need to use the same set of transformations to different queries or values, so in this case you can create custom function using Power Query that can be reused as many times as you need.

Lets see how to create a custom function in Power BI.

To demonstrate this, we will be using a SQL Server Database: Adventureworks2019 and table TransactionHistory.

SELECT [TransactionID]
,[ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionDate]
,[TransactionType]
,[Quantity]
,[ActualCost]
,[ModifiedDate]
FROM [AdventureWorks2019].[Production].[TransactionHistory]
WHERE [ProductID]=784

Lets connect to SQL Server, expand Get data in ribbon bar and select SQL Server.

Provide server details, in the SQL Server database window, select Advanced options, and paste the SQL query into the SQL statement box and then select OK.

After that, you will see a preview of data, click on Transform Data button.

After clicking on Transform Data button, a Power Query Editor window opens.

Now we will Create a parameter, go to Home tab, and select Manage parameters then New parameter.

Next, you will see a Manage Parameters dialog box opens.

Change the default parameter name to something more meaningful so that its purpose is clear. In this case, Lets give it a name as ProductId.

After that select Text from the Type list and then select Any value from the Suggested value list.

Next click on OK button.

You can see, a parameter ProductId is created with current value 784 as shown below.

Now, you need to adjust the code in SQL query to assess your new parameter.

Right click on Query1 and then select Advanced editor from context menu.

You can see, a Advanced Editor dialog box opens.

There you can see a query and query has a default filter on productid so data is filtered for productid 784.

Now we will modify this query and replace that hard code productid value with parameter ProductId that you have created, so the query takes values through parameters and filter the data based on provided value.

Lets replace the existing value in the execute statement with an ampersand (&) followed by parameter name ProductId that will be &ProductId, as shown below.

After clicking on Done button, lets confirm that the query takes values through parameters and filter the data based on provided value.

You can run a test by selecting the parameter query and entering a new value in the Current Value box.

Lets provide the current value 318 as shown below.

You may get a warning icon that might display next to the query. If so, select that query to view the warning message, which states that permission is required to run this native database query. Select Edit Permission and then select Run.

When the query runs successfully, the parameter will update and you can see a data is filtered for new productid 318 that can be seen in preview window.

Now go to Home tab and click on Close & Apply button to save the changes.

Now, you can apply the parameter to the report.

Go to Transform data again and then select Edit parameters from Manage Parameters.

Now in Edit Parameters window, enter a new value and then select OK.

Lets enter the value 316, and click on OK button.

If you see a warning icon, then select Edit Permission and then select Run.

You can see it filters the data for productid 316 as shown below.

After that, click on Close & Apply to save the changes.

So far we have seen, how to display data for one particular value of productid at a time using parameter.

Now we will see how to display data for multiple productid values at the same time using custom function.

Creating a custom function

We will continue in same file, as we want display data for multiple productid values.

Here we have a excel file which contains unique productid as shown below, lets import this file.

In Home tab, expand New source and click on Excel, and browse the excel file in Power BI desktop.

After that you can see, a Navigator dialog box opens.




Now select the sheet1 and you can see the sample of data in preview window.

Next, click on load button.

Now you can see, the excel file is loaded into data model.

Also change the column data type to Text so that it matches the parameter type and you avoid data conversion problems.

Select the column productid then expand the Data Type drop down and select the Text.

Next, we will create a function that will pass the new ProductId query into Query1.

Right click on Query1, and select Create Function.. from context menu.

Once you click on Create Function.., a create function dialog box opens, now give a meaningful name to function and click on Ok button.

Lets give it a name as GetProductId, and click on OK button.

Now you can see a new function or function query in the Queries pane as shown below.

Now disable the load for source query Query1, to ensure that Query1 does not show up in the field list for the report, which could potentially confuse users.

Just right click on Query1 again and then uncheck Enable load (selected by default) to disable the load.

After clicking on Continue button.

Lets rename a sheet1 query that you loaded from the Excel file, lets give it a name as ProductIdList.

After renaming it, select the query and then, on the Add Column tab, select Invoke custom function to run the custom function that you have created.

On the Invoke Custom Function window, select your function that is getproductid from the Function query list.

The New column name will update automatically and the table that contains the values that you are going to pass through the parameter will be selected by default.

Select OK and, if necessary, run the native query.

Now you will see a new column for your GetProductTransactiondetails function will appear next to the ProductId column.

 2,636 total views,  1 views today