Introduction
Often at times, you may come across situations where you need to calculate a column repeatedly multiple times in the same Power BI report or across multiple reports. Although you can use the calculated columns to some extent, these are not robust and not reusable. In order to reuse the same piece of code, you need to create custom functions using Power Query and then use them in the columns.
In this article, I’m going to explain how we can create and use a custom function in Power BI.
Solution
For the sake of this article, I’ll demonstrate how to calculate the age of a person from the date of birth using the custom function.
Disclaimer – For simplicity, the formula used to calculate age in this article is just used to demonstrate the use of the function and might not be accurate (since we calculate the difference from the current date in years). In case you need more information on how to calculate age from the date of birth you can follow the logic mentioned in this thread.
I have imported a simple dataset into the Power BI environment. This dataset contains two columns –
- User – Name of the user
- DOB – Date of Birth of the user
In order to create the custom function, you can follow the steps below.
- Right-click on the query “Users” and select “Create Function…”.
Figure 2 - Creating Function - You might receive a warning stating that there are no parameters in the query. Click on Create.
Figure 3 - No Parameters Found - The Create Function dialog appears. Provide the Function Name as “GetAge” and click OK.
Figure 4 - GetAge Function - Notice that the function is created on the left-hand query panel. Click the GetAge function and select Advanced Editor from the ribbon on top.
Figure 5 - Advanced Editor - In the Edit Function dialog that appears, click OK.
Figure 6 - Edit Function - The Advanced Editor dialog appears. Here, you need to enter the formula as below and click Done. In the formula below, we extract the year values from both the DOB and Current Date, and then subtract both the Year values.
let GetAge = (DOB) => Date.Year(DateTime.LocalNow()) – Date.Year(DOB) in GetAge ?Figure 7 - Advanced Editor GetAge Function - Now that our custom function is defined, we can go ahead and use it in our dataset. Click on the table “Users”, select Custom Column under the Add Column tab on the menu bar.
Figure 8 - Adding Custom Column - In the Custom Column dialog that appears, provide the New Column Name as “Age” and Custom Column Formula as “GetAge([DOB])”. Click OK.
Figure 9 - Custom Column Formula - Now, you can see that a new column “Age” has been added to the dataset that calculates the age of both the users present.
Figure 10 - New Column Added
For your reference, I have added the sample Power BI report within this article and it is available to download and use for free. Please follow this link to download the sample report.
Next Steps
In this tip, I have discussed how to create custom functions in Power BI using Power Query (M) and use it in another custom column. The next steps are as follows:
- Understanding Power Query M Functions.
- Read more about Date Functions in Power Query.
