Extracting Year, Month, or Day from a Date in Power Query

The date column is the foundation when working with calendar tables in Power Query. It contains all relevant information about the year, month, and day. However, it’s often convenient to have date components available separately for your data model. That makes it easy to filter on them in your data tables or to reference them in your DAX formulas. In this article, you learn how to extract year, month or day values from a date.

Table of contents

• Extracting the Year from a Date Value
  • Using the Date.Year Function
  • Using the Date.ToText Function
  • When to Use Each Function
• Extracting the Month from a Date Value
  • Using the Date.Month Function
  • Using the Date.MonthName Function
  • Using the Date.ToText Function
  • When to Use Each Function
• Extracting the Day from a Date Value
  • Using the Date.Day Function
  • Using the Date.DayOfWeek Function
  • Using the Date.DayOfYear Function
  • Using the Date.DaysInMonth Function
  • Using the Date.DayOfWeekName Function
  • Using the Date.ToText Function
  • When to Use Each Function
• Conclusion

Extracting the Year from a Date Value

In Power Query, you can use a range of functions to extract the year value from a date. Let’s look at the most important ones.

Using the Date.Year Function

To extract the year from a date, you can use the Date.Year function. This function takes a date value and returns the year as a number. Here’s an example:

let
  myDate= #date( 2024, 6, 29 ),
  extractYear = Date.Year( myDate ) // Output: 2024
in
  extractYear 

In this example, we create a date value myDate representing June 29, 2024. The Date.Year function extracts the year from this date, which is 2024.

Using the Date.ToText Function

If you prefer to have the year as a text value, you can use the Date.ToText function with a custom formatting string. This is particularly useful if you need the year in a specific text format, such as for display purposes. Here’s how to do it:

let
  myDate= #date( 2024, 6, 29 ),
  extractYear = Date.ToText( myDate, "yyyy" ),    // Output: "2024"
  extractshortYear = Date.ToText( myDate, "yy" )  // Output: "24"

in
  extractshortYear 

In the extractYear step, the Date.ToText function converts the date to text, formatted as “yyyy”, which represents the year. The output is “2024”, but as a text string. The step after, called extractShortYear, extracts the last two characters from the year and returns “24”. This operation is useful when you need the year in a text format, perhaps for concatenation with other text values.

When to Use Each Function

• Use Date.Year: When you need the year as a number, such as for when you need to year value in further calculations or as separate column.
• Use Date.ToText: When you need the year as text, especially for when combining with other text values or when you require a custom format.

Extracting the Month from a Date Value

Extracting the month from a date in Power Query can also be done using several functions, depending on whether you want the month as a number or a name. Let’s look at some examples.

Using the Date.Month Function

The simplest way to extract the month from a date is by using the Date.Month function. This function returns the month as a number. Here’s an example:

let
  myDate= #date( 2024, 6, 29 ),
  extractMonth = Date.Month( myDate ) // Output: 6
in
  extractMonth 

Here, the Date.Month function extracts the month from the date, which is 6.

Using the Date.MonthName Function

If you prefer the month name instead of the month number, you can use the Date.MonthName function. This functions also supports an optional culture code to format the date according to a specific locale.

let
  myDate= #date( 2024, 6, 29 ),
  extractMonth = Date.MonthName( myDate ), // Output: "June"
  extractFrenchMonth = Date.MonthName( myDate, "fr-FR" ) // Output: "juin"
in
  extractFrenchMonth 

In this example, we use the same date value myDate. The Date.MonthName function converts this date to the month name, based on your computer’s default language settings. Since my computer is set to English, this results in “June”. However, by specifying the ‘fr-FR’ culture code, the month name is converted to French, resulting in “juin”.

Using the Date.ToText Function

To format the month to a specific string, use the Date.ToText function. For instance, this function can return the month number as a single or double-digit text value with the following formatting strings:

let
  myDate= #date( 2024, 6, 29 ),
  singleDigitMonth = Date.ToText( myDate, [Format = "%M"] ), // Output: "6"
  doubleDigitMonth = Date.ToText( myDate, [Format = "MM"] )  // Output: "06"
in
    doubleDigitMonth

This function also supports both a format string and a culture parameter. Here are two ways that show how you can use them:

let
  myDate= #date( 2024, 6, 29 ),
  extractMonthName_nlNL = 
    Date.ToText( myDate, [Format = "MMMM", Culture = "nl-NL"] ), // Output: "juni"
  extractMonthName_enUS = 
    Date.ToText( myDate, [Format = "MMM", Culture = "en-US"] )   // Output: "Jun"
in
    extractMonthName_enUS 

In these examples, Date.ToText is used to format the month name according to different cultures. The first example returns the full month name in Dutch (“juni”), and the second example returns the abbreviated month name in English (“Jun”). Read this article for all the available formatting strings for dates.

When to Use Each Function

• Use Date.Month: When you need the month as a number, such as for arithmetic operations or when the month will be part of further data transformations.
• Use Date.MonthName: When you need the month as a name, especially for display purposes or when you want to format the month name according to different locales.
• Use Date.ToText: When you need the month formatted in a custom format. Good examples are returning the day in single or double-digit text or returning with a custom formatting string.

Extracting the Day from a Date Value

You can also extract the day value from a date in several ways.

Using the Date.Day Function

The Date.Day function extracts the day from a date and returns it as a number. This number represents the day of the month. Here’s an example:

let
  myDate= #date( 2024, 6, 29 ),
  extractDay = Date.Day( myDate ) // Output: 29
in
  extractDay

In this example, the Date.Day function extracts the day from this date, which is 29.

Using the Date.DayOfWeek Function

To get the day of the week, you can use the Date.DayOfWeek function. This function returns the day of the week as a number, where 0 represents Monday and 6 represents Sunday.

let
  myDate= #date( 2024, 6, 29 ),
  extractDay = Date.DayOfWeek( myDate ) // Output: 5
in
  extractDay 

In this example, the Date.DayOfWeek function extracts the day of the week from the date myDate, which is June 29, 2024. The result indicates that this date falls on a Saturday, which has a zero-based index value of 5.

If you need to calculate the day of the week starting from a different day, you can provide an optional Day.Type parameter. For instance, if you want the week to start on Wednesday, you can specify that:

let
  myDate= #date( 2024, 6, 29 ),
  extractDay = Date.DayOfWeek( myDate, Day.Wednesday ) // Output: 3
in
  extractDay 

In this case, the function returns 3, indicating that June 29, 2024, is the fourth day of the week when the week starts on Wednesday.

Using the Date.DayOfYear Function

The Date.DayOfYear function extracts the day of the year from a date and returns it as a number. This number represents the position of the day within the year. Here’s an example:

let
  myDate = #date(2024, 6, 29),
  extractDay = Date.DayOfYear(myDate) // Output: 181
in
  extractDay 

In this example, the Date.DayOfYear function extracts the day of the year from the date June 29, 2024. The function returns 181, indicating that it is the 181st day of the year.

Using the Date.DaysInMonth Function

The Date.DaysInMonth function extracts the total number of days in the month from a date and returns it as a number. Here’s an example:

let
  myDate = #date(2024, 6, 29),
  extractDay = Date.DaysInMonth(myDate) // Output: 30
in
  extractDay 

In this example, the Date.DaysInMonth function determines the number of days in the month of the date myDate, which is June 2024. The function returns 30, indicating that June has 30 days.

Using the Date.DayOfWeekName Function

The Date.DayOfWeekName function extracts the name of the day of the week from a date. You can use this as a standalone field in your report visuals or to combine with other text values in your columns. Here’s an example:

let
  myDate = #date(2024, 6, 29),
  dayOfWeekName = Date.DayOfWeekName(myDate) // Output: "Saturday"
in
  dayOfWeekName

You can optionally provide a culture code to be more specific about the function’s formatting:

let
  myDate = #date(2024, 6, 29),
  dayOfWeekName = Date.DayOfWeekName(myDate, "ES-es" ) // Output: "sábado"
in
  dayOfWeekName

In this example, the Spanish day of week name for June 29, 2024 is “sábado”.

Using the Date.ToText Function

To format the day of the week in a particular way or language, use the Date.ToText function. This function is useful for custom or locale-specific formats.

let
  myDate= #date( 2024, 6, 9 ),
  extractDay_singleDigit = Date.ToText( myDate, [Format = "%d"] ) // Output: 9
  extractDay_doubleDigit = Date.ToText( myDate, [Format = "dd"] ) // Output: 09
  extractDay_nameShort = Date.ToText( myDate, [Format = "ddd"] )  // Output: "Sun"
  extractDay_Name = Date.ToText( myDate, [Format = "dddd"] )      // Output: "Sunday"
in
    extractMonthName_enUS 

In this example, we format the date myDate (June 9, 2024) in various ways:

• %d: Single-digit day (9)
• dd: Double-digit day (09)
• ddd: Abbreviated day name (“Sun”)
• dddd: Full day name (“Sunday”)

When to Use Each Function

• Use Date.Day: When you need the day of the month as a number value.
• Use Date.DayOfWeek: When you need the day of the week as a number, either for standard weeks (0 = Sunday) or custom start days.
• Use Date.DayOfYear: When you need to know the ordinal position of a date within a year. This is useful for calculating progress through the year, determining the number of days remaining in the year, or for any analysis that requires the day-of-year information.
• Use Date.DaysInMonth: When you need to know the total number of days in a month. This is useful for calculating monthly statistics, validating date ranges, or for any analysis that requires knowing the number of days in a specific month.
• Use Date.DayOfWeekName: When you need the name of the day of the week as text. For alternative formatting, like abbreviated version, refer to the Date.ToText function.
• Use Date.ToText: When you need the day formatted as text, either short name, or full name or for as displaying a single or double digit number as text.

Conclusion

The M language in Power Query has a select of handy functions for working with dates. You can pull out basic pieces like the year, month, or day number. But there’s more! If you need the name of a month or day of the week, you can use functions like Date.MonthName and Date.DayOfWeekName. And for really specific formats, the Date.ToText function is super flexible with custom format strings.

By using these functions, you can shape date information to fit your needs perfectly. So next time you’re building a calendar table, you now know what options are there.