Transform a date to a day of the year

[JonathanJohns]

Good morning,

I've got a question about a transformation of the date. 

I would like to do this operation :

Date	Day
01/01/2017	1
02/01/2017	2
03/01/2017	3
04/01/2017	4
05/01/2017	5
-------	-----
-------	-----
01/01/2018	1
02/01/2018	2
03/01/2018	3
04/01/2018	4
05/01/2018	5

I tried to use the function Day but this one works only for day in month and not for a year. Do you have any idea to do it please ?

Thank you for your anwer.

[jthomson]

If you're not wanting to use Power Query, then it'd also be possible to use DAX with something like:

DayofYear = DATEDIFF(DATE((YEAR[yourdatecolumn]-1),12,31),[yourdatecolumn],DAY)

which'll count the number of days between the last day of the previous year and the date in question

[Anonymous · Accepted solution]

It can be done in the query editor. Open the table that contains date field in query edition and right click on the field that has dates and select " Add columns from Example". A new column will be added, then double click on a cell in the new column you will see a drop down as below-select the circled option "Day of Year"

[JonathanJohns]

It works perfectly thank you !