Invoking custom functions 289
You can see the newly created DaysFromCurrentDate column.
Figure 8.35 – New column created
Every time you refresh your data, the number will update according to the current date.
You can see it is easy to create custom columns and the more you become confident with M functions, the easier it will be to create more complex content. You will be able to concatenate, perform complex calculations, and refer to parameters as you would do with M functions in the Advanced Editor.
Invoking custom functions
Power Query offers you the ability to enrich existing tables with additional columns in many different ways, as you have seen in previous recipes, but it also allows you to use custom functions defined as expressions that take some variables as inputs to
return a result value. In this recipe, we will see how to create a function, define function parameters, and invoke that function to generate an output.
Getting ready
For this recipe, you need to download the FactResellerSales CSV file. In this example, we will refer to the C:\Data folder.
290 Adding Value to Your Data
How to do it…
Once you open your Power BI Desktop application, you are ready to perform the following steps:
1.Click on Get data and select the Text/CSV connector.
2.Browse to your local folder where you downloaded the FactResellerSales CSV file and open it. The following window with a preview of the data will pop up; click on Transform Data:
Figure 8.36 – CSV data preview
Invoking custom functions 291
3.Now, right-click on the Queries pane space and click on New Query and then
Blank Query.
Figure 8.37 – Blank query creation
4.In our example, we will define a function to calculate the net sales amount when applying different discount values (if you have SalesAmount equal to 10, you first apply a discount of 10%, and then you subtract the total cost from that discounted value). After having created a new blank query, browse to the Home tab and open
Advanced Editor.
Figure 8.38 – Blank query display
292Adding Value to Your Data
5.A blank query contains the M code text you see in the following screenshot and here, we can define our own function:
Figure 8.39 – Blank query in the Advanced Editor
6. Enter the following code to create a function:
(OldSalesAmount as number, Discount as number, TotalCosts as number) =>
let
NetSales = OldSalesAmount - (OldSalesAmount * Discount
) - TotalCosts
in
NetSales
The formula is divided into the following parts:
a)Definition of input values: OldSalesAmount, Discount, and TotalCosts. After you define these parameters, you will add the => expression to introduce the function and the subsequent part, which starts with let.
b)Function definition: Function formula to calculate NetSales, which is given by OldSalesAmount - (OldSalesAmount * Discount ) – TotalCosts.
c)Value returned: This is introduced by the in clause.
Invoking custom functions 293
Have a look at how it looks in the Advanced Editor and click on Done.
Figure 8.40 – Function definition in the Advanced Editor
7. See how the function appears in the Power Query UI.
Figure 8.41 – Custom function parameters
294 Adding Value to Your Data
Let's also try to manually input some values to see how it works. Enter 5 for
OldSalesAmount, 0,1 for Discount (meaning 10%), and 3 for TotalCosts, and then click on the Invoke button.
Figure 8.42 – Enter Parameters
8.You will see the NetSales value when a 10% discount is applied as the output of the invoked function.
Figure 8.43 – Invoked Function
9.Now, let's delete Invoked Function under the Queries section on the left side of the UI and try to create an invoked custom function to apply this calculation to the
FactResellerSales data.
Figure 8.44 – Deleting Invoked Function
Invoking custom functions 295
10.Rename the function query to fxNetSales to identify it easily as the function to calculate NetSales.
Figure 8.45 – Renaming a function
11.Now select the FactResellerSales query, browse to the Add Column tab, and click on Invoke Custom Function to apply the fxNetSales function, defining as inputs the columns from FactResellerSales.
Figure 8.46 – Invoke Custom Function button
12.The Invoke Custom Function window will pop up and from here, you can define which function and what input variables to use. Name the new column NetSales and select the fxNetSales function from the Function query dropdown.
Figure 8.47 – Invoke Custom Function window
296Adding Value to Your Data
13.Select the SalesAmount column for the OldSalesAmount input, enter 0,1 for the Discount input, and then click on the input type icon and select Column Name for the TotalCosts input.
Figure 8.48 – Variables definition
14. Then select the TotalProductCost column as the input column for TotalCosts.
Figure 8.49 – Variable selection
Invoking custom functions 297
15. After having defined the input variables, click on OK.
Figure 8.50 – Variables defined
16.A new column will be added to the query as the output of the function with the variables you have defined.
Figure 8.51 – Newly generated column