Date.DayOfYear

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Date.DayOfYear

Understanding the Date.DayOfYear Function

The Date.DayOfYear function is a member of the Date class in Power Query M. The function takes a single argument, which is a date value. The function then returns the day of the year for that date value. For example, if the date is January 1, the function returns 1. If the date is December 31, the function returns 365 (or 366 in a leap year).

The syntax for the Date.DayOfYear function is as follows:

Date.DayOfYear(date as date) as number

The M Code Behind the Function

The M code behind the Date.DayOfYear function is relatively simple. The function simply takes the date value and subtracts the first day of the year (January 1) from it. This gives the number of days that have elapsed since the beginning of the year. The code for the Date.DayOfYear function is as follows:

let

Date.DayOfYear = (date as date) as number =>

Date.Day(date) – Date.Day(Date.FromText(“1/1/” & Number.ToText(Date.Year(date)), “M/d/yyyy”)) + 1

in

Date.DayOfYear

Let's break down this code into its component parts:

- The "let" statement is used to define a function in M. The function is named Date.DayOfYear.

- The function takes a single argument, which is a date value.

- The function returns a number value, which is the day of the year for the given date value.

- The "Date.Day" function is used to get the day of the month for the given date value.

- The "Date.FromText" function is used to create a new date value for January 1 of the same year as the given date value. This function takes a text value in the format "M/d/yyyy" as its argument.

- The "Number.ToText" function is used to convert the year of the given date value to a text value.

- The "Date.Day" function is used again to get the day of the month for the January 1 date value.

- Finally, the two day values are subtracted and 1 is added to get the day of the year for the given date value.

Examples of Using the Date.DayOfYear Function

Let's look at some examples of using the Date.DayOfYear function in Power Query M.

Example 1: Basic Usage

Suppose we have a table of dates in the format "M/d/yyyy" and we want to get the day of the year for each date. We can use the following M code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUQoA”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t])),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type date}}),

#”Added Custom” = Table.AddColumn(#”Changed Type”, “DayOfYear”, each Date.DayOfYear([Date]))

in

#”Added Custom”

This code creates a new column called "DayOfYear" that contains the day of the year for each date in the table.

Example 2: Using the Function in Calculated Columns

Suppose we have a table of financial transactions that contains a column for the transaction date. We want to create a calculated column that contains the number of days between each transaction and the beginning of the year. We can use the following M code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUQoA”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Amount = _t])),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type date}, {“Amount”, type number}}),

#”Added Custom” = Table.AddColumn(#”Changed Type”, “DaysSinceYearStart”, each Date.DayOfYear([Date])),

#”Grouped Rows” = Table.Group(#”Added Custom”, {“DaysSinceYearStart”}, {{“TotalAmount”, each List.Sum([Amount]), type number}})

in

#”Grouped Rows”

This code creates a new column called "DaysSinceYearStart" that contains the day of the year for each transaction date. We then group the rows by the day of the year and calculate the total amount for each group. This gives us a summary of the financial transactions for each day of the year.

The Date.DayOfYear function in Power Query M is a powerful tool for working with dates in Microsoft Excel. By understanding the M code behind the function, we can better utilize its features and capabilities. Whether we are working with financial transactions or simply need to know the day of the year for a given date, the Date.DayOfYear function can help us achieve our goals more efficiently and effectively.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)