Day Number of Year, Power Query Custom Function

There are number of Date and DateTime built-in functions in Power Query which are really helpful. However there is no function that calculate Day number of year. Long time ago, I’ve created this function as an example. So I’ve thought it would be helpful for you to post it here with explanation. Through this post you will also learn;

• how to create Custom Function
• how to use Generators as loop structure
• and how to user Error Handling.

Let’s consider this date as today’s date : 28th of July of 2015 (this is actually the date of this blog post)

There might be number of methods to calculate the day number of year for this date (which is 209). I just use one of them here. Steps are as below;

• fetch number of days for each month from January of this year (of the date above) to previous month (of the date above).
• Calculate sum of values above will give me the number of days in all month prior than this month.
• add the day number of the date to the calculated sum above.

Some of calculations can be helped through with Power Query Date functions. So let’s start;

1 – Create a function in Power Query called DayNumberOfYear as below

If you don’t know where to write below code:

• Open Excel, Go to Power Query Tab, Click On Get Data from Source, Blank Query, In the Query Editor window go to View tab, and click on Advanced Editor.
• Open Power BI, click on Edit Queries, In the Query Editor window go to View tab, and click on Advanced Editor.

I’ve put some comments in above script to help you understand each line. In general DayNumberOfYear is name of the function. It accepts an input parameter “date”. and convert the parameter from text value to DateTime. the last line of the code calls the function with specific date (“07/28/2015″).

Read the rest of blog post here:

https://www.radacad.com/day-number-of-year-power-query-custom-function