Date.WeekOfYear

D

Back to top

T

Back to top

#

Back to top

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

What is the Date.WeekOfYear Function?

The Date.WeekOfYear function is a Power Query M function that returns the week number of a given date. The function takes a single argument, which is the date to be evaluated. The week number is determined based on the ISO 8601 standard, which defines the first week of the year as the week containing the fourth day of January.

Understanding the M Code Behind Date.WeekOfYear

To understand how the Date.WeekOfYear function works, we need to take a closer look at the M code behind it. The M code for the function is as follows:

(Date) =>

let

DayOfWeek = Date.DayOfWeek(Date.AddDays(Date,”-01-04″), Day.Monday),

YearStart = Date.AddDays(Date,”-01-01″),

WeekNumber = Number.RoundDown((Date – YearStart + (DayOfWeek-1))/7) + 1

in

WeekNumber

Let's break this code down into its individual components and understand what each part does.

The Date Parameter

The first line of the code defines the Date parameter, which is the date for which we want to find the week number. This parameter is passed to the function when it is called, and the function uses it to determine the correct week number.

DayOfWeek Calculation

The next line of the code calculates the DayOfWeek value, which is the day of the week that corresponds to January 4th of the year in question. This value is calculated by subtracting one day from January 4th and then using the DayOfWeek function to determine the day of the week.

YearStart Calculation

The YearStart variable is then calculated as the first day of the year. This is done by subtracting the number of days that have passed since January 1st of the given year.

WeekNumber Calculation

Finally, the WeekNumber value is calculated using the following formula:

(Number.RoundDown((Date – YearStart + (DayOfWeek-1))/7) + 1)

This formula subtracts the YearStart value from the given date, adds the DayOfWeek value, and then divides the result by 7 to get the number of weeks that have passed since the start of the year. The Number.RoundDown function is used to round this value down to the nearest whole number, and 1 is added to the result to get the correct week number.

The Date.WeekOfYear function is a powerful tool that allows users to determine the week number of a given date. By understanding the M code behind the function, we can gain a deeper understanding of how it works and how we can use it to perform complex data transformations and analysis. With this knowledge, we can take our Power Query M skills to the next level and unlock the full potential of this powerful tool.

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)