Power Query: Getting Something Out of a Date
4 July 2018
Welcome to our Power Query blog. This week, I look at some useful ways to extract data from dates in M.
I will take a look as some Date() M functions that can be used extract data from an existing date, for example, when I want to know the day or year of a particular date. I will give an example for each function.
Date.Day
Date.Day(datetime as datetime) as nullable number
Returns the day for a datetime value.
This is a simple M function, which will extract the day of the month.
The M functionality I have used is
= Date.Day([Final Payment])
The day of the month has now been extracted.
Date.DayOfWeek
Date.DayOfWeek(datetime as any, optional firstDayOfWeek as nullable number) as nullable number
Returns a number between 0 and 6 representing the day of the week in the provided datetime value. This function takes an optional day value, firstDayOfWeek, to set the first day of the week for this relative calculation. Valid values are: Day.Sunday, Day.Monday, Day.Tuesday, Day.Wednesday, Day.Thursday, Day.Friday, and Day.Saturday.
I have not included the Microsoft help information about the default. The help says that the default is that the week starts on a Sunday, but when I tested it, the default is Monday! If I put a second parameter in, then I can indicate that the week should start on a Sunday (I can use Day.Sunday as the parameter). I might like to think that the week starts on a Friday, and I can set the function to agree with me, but I can’t think of a business reason to do this!
I will use the expense data for my fictional salespeople, to find out more about the date that I will be paying them their expenses.
I create a new custom column to show me the day.
The M formula I have used is
= Date.DayOfWeek([Final Payment], Day.Sunday)
I specified that the week starts on a Sunday.
I have my day of payment represented as a number, so that 0 is Sunday, 1 is Monday and so on.
Date.DayOfWeekName
Date.DayOfWeekName(date as any, optional culture as nullable text)
Returns the day of the week name for the provided date and, optionally, a culture.
An example of culture is ‘en-US’, to give me US English weekday names.
The M functionality I have used is
= Date.DayOfWeekName([Final Payment])
The name of the day is shown.
Date.DayOfYear
Date.DayOfYear(datetime as datetime) as nullable number
Returns a number that represents the day of the year from a datetime value.
This will give me the day number within the year that the final payment takes place.
The M formula I have used is
= Date.DayOfYear([Final Payment])
I have the day number (in terms of the year) that my payment takes place.
Date.DaysInMonth
Date.DaysInMonth(datetime as datetime) as nullable number
Returns the number of days in the month from a datetime value.
This function could be useful when comparing sales between months – if we are comparing July and February then it makes sense to take into account the number of days involved.
The M functionality I have used is
= Date.DaysInMonth([Final Payment])
The number of days in the month of the final payment is shown. There are a number of other extractions that I can do, and I have summarised all of them in the final image at the end of this blog.
Date.Year
Date.Year(datetime as datetime) as nullable number
Returns the year from a datetime value.
An example M formula might be (used in the final image)
= Date.Year([Final Payment])
Date.Month
Date.Month(datetime as datetime) as nullable number
Returns the month from a datetime value.
An example M formula might be (used in the final image)
= Date.Month([Final Payment])
Date.MonthName
Date.MonthName(date as any, optional culture as nullable text)
Returns the name of the month component for the provided date and, optionally, a culture.
An example M formula might be (used in the final image)
= Date.MonthName([Final Payment])
Date.QuarterOfYear
Date.QuarterOfYear(datetime as datetime) as nullable number
Returns a number between 1 and 4 for the quarter of the year from a datetime value.
An example M formula might be (used in the final image)
= Date.QuarterOfYear([Final Payment])
Date.WeekOfMonth
Date.WeekOfMonth(datetime as datetime) as nullable number
Returns a number for the count of week in the current month.
An example M formula might be (used in the final image)
= Date.WeekofMonth([Final Payment])
Date.WeekOfYear
Date.WeekOfYear(datetime as datetime) as nullable number
Returns a number for the count of week in the current year.
An example M formula might be (used in the final image)
=Date.WeekOfYear([Final Payment])
This final screenshot shows all of the remaining functions:
Come back next time for more ways to use Power Query!
