This article demonstrate how to create a custom function in Power Query that returns the difference of two dates.
Custom function takes a set of input from users and based on that input it evaluates the M code logic and return a single value.
Creating a custom function is useful, if have any logic that you want to reuse many times. You can simply create a custom function and can invoke it where ever you need it.
As you can see, here we have a table which contains the customer’s order details.
Let’s create a function named OrderAging which return the difference of Order Date and Delivery Date.
For this, right click on table then select Create Function.. from context menu as shown below.
You might receive a warning indicating that query does not reference any parameters. You just need to click on Create button.
After that, a pop up window named Create Function appears which ask you to provide the name for your function, just provide the function name as shown below.
After that click on OK button, you will see that a function named OrderAging is created.
Now just select the function and click on Advance Editor, here we will write the logic for function.
Once you click on Advance Editor, a Edit Function window appears just click on OK button.
Now write the below function code.
(OrderDate as date, DeliveryDate as date) => let source = Duration.Days(DeliveryDate - OrderDate) in source
Once you done with this, you can see now the function have two parameters, one for Order date and another for Delivery date.
Next, we will create a custom column which will use function to show the order date and delivery date difference.
Let’s create a customer column. First, select CustomerOrder table then click on Add Column tab after that click on Custom Column tab as shown below.
Now, you will see a Custom Column window appear. Now you need provide the name for column and write the M code for custom column as shown below.
=OrderAging([OrderDate], [DeliveryDate])
Once you done with this, click on OK button.
Now you can see that it returns the Order Aging, a day difference between Order date and Delivery date.
Also Read..
Create and invoking Custom Function
