Extended Date Table (Power Query M function)

Ericadyson · November 21, 2020, 8:29am

As ever, Melissa, you take the time to explain, step by step, what and why and how. The date table as a query is wonderful and all goes to help us understand M code better. Thanks so much. Thanks to you and everyone and E-DNA, I’m moving up the ladder, rung by rung!

ericet · January 13, 2021, 10:15pm

Hello Melissa,

Awesome script, but I need it in the french language. Any suggestion for me ?
I just need to translate the text nothing else.

Should I just translate the text in “qoutes” and that should work ?

Thanks

Eric (Montreal, Canada)

Melissa · January 13, 2021, 10:45pm

Hi @ericet,

Welcome to the Forum, great to have you here.

I’m afraid not, that’s just the initial naming of the column. Thing is that once a column has been created it can be reference by its name in square brackets so “Date” becomes [Date] for example.

I think there are two ways you can approach this either copy the entire script to a text editor / visual studio code and first replace the column name like “Date” followed by a replace all of the text [Date]
OR
Keep it as is and rename all columns after you’ve invoked the function.
You can even choose to copy that Table.RenameColumns step back to the function for future use.

I hope this is helpful.

ericet · January 13, 2021, 11:01pm

Thanks for the quick reply, looking at the code in more détail, I do not see Monday, Tuesday, Wednesday etc for the name of the days ?

Melissa · January 13, 2021, 11:29pm

Correct those are created with an M function, you can check in the PBIX file settings if that’s set to your region.

ericet · January 13, 2021, 11:57pm

Found a great YouTube with a link to download a PBIX file that has exactly what I need. So, I don’t have to reinvent the wheel. https://youtu.be/jpZ96T3wdjc

I would have prefered your calendar but time is pressing maybe someone already made different language versions of your script.

Thanks

chad.sharpe · February 4, 2021, 3:38pm

Melissa:

let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number, optional Holidays as list, optional WDStartNum as number ) as table =>
  let
    FYStartMonth = if List.Contains( {1..12}, FYStartMonth ) then FYStartMonth else 1,
    //the WDStartNum parameter allows you to set Weekday numbering running from 0-6 or 1-7 but first day of the week will remain Monday
    WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
    CurrentDate = Date.From(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
    TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type number),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
    InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month & Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayOfWeek", each Date.DayOfWeek([Date]) + WDStart, Int64.Type),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek( [Date], Day.Monday), type date),
    InsertMonthEnding = Table.AddColumn(InsertWeekEnding, "MonthEnding", each Date.EndOfMonth([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertMonthEnding, "ISO Weeknumber", each
      if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
      then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
      else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
      then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
    InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )),  Int64.Type),
    BufferTable = Table.Buffer(Table.Distinct( InsertISOyear[[ISO Year], [DateInt]])),
    InsertISOday = Table.AddColumn(InsertISOyear, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])),  Int64.Type),
    InsertCalendarWk = Table.AddColumn(InsertISOday, "Week & Year", each Text.From([ISO Year]) & "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), type text ),
    InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100,  Int64.Type),

    InsertMonthnYear = Table.AddColumn(InsertWeeknYear , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
    AddFY = Table.AddColumn(InsertQuarternYear, "Fiscal Year", each "FY" & (if [MonthOfYear] >= FYStartMonth then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), type text),
    AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
    AddFM = Table.AddColumn(AddFQ, "Fiscal Period", each if [MonthOfYear] >= FYStartMonth then [MonthOfYear] - (FYStartMonth-1) else [MonthOfYear] + (12-FYStartMonth+1), type text),

    InsertIsAfterToday = Table.AddColumn(AddFM, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
    InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([Date], Day.Monday) > 4 then false else true, type logical),
    InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),
    InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", each if [IsWorkingDay] = true and [IsHoliday] <> true then true else false, type logical),
    InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text),

    //InsertDayOffset = Table.AddColumn(InsertDayType, "DayOffset", each Number.From([Date] - CurrentDate), type number),  //if you enable DayOffset, don't forget to adjust the PreviousStepName in the next line of code.
    InsertWeekOffset = Table.AddColumn(InsertDayType, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, type number),
    InsertMonthOffset = Table.AddColumn(InsertWeekOffset, "MonthOffset", each ((12 * Date.Year([Date])) +  Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) +  Date.Month(Date.From(CurrentDate))), type number),
    InsertQuarterOffset = Table.AddColumn(InsertMonthOffset, "QuarterOffset", each ((4 * Date.Year([Date])) +  Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) +  Date.QuarterOfYear(Date.From(CurrentDate))), type number),
    InsertYearOffset = Table.AddColumn(InsertQuarterOffset, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),

    IdentifyCurrentDate = Table.SelectRows(InsertYearOffset, each ([Date] = CurrentDate)),
    CurrentYear = IdentifyCurrentDate{0}[Year],
    CurrentMonth = IdentifyCurrentDate{0}[MonthOfYear],
    InsertFYoffset = Table.AddColumn(InsertYearOffset, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - 
      (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
    RemoveToday = if EndDate < CurrentDate then Table.SelectRows(InsertFYoffset, each ([Date] <> CurrentDate)) else InsertFYoffset,
    InsertCompletedWeek = Table.AddColumn(RemoveToday, "WeekCompleted", each [WeekEnding] < Date.From(Date.EndOfWeek(CurrentDate)), type logical),
    InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "MonthCompleted", each [MonthEnding] < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
    InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),
    InsertChangedType = Table.TransformColumnTypes(InsertCompletedQuarter,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}})
  in
  InsertChangedType
in
fnDateTable

GREAT!!!
I’ve been waiting for some time to come across a date table with FY details built into it…

michellepace · February 8, 2021, 10:09am

Hi @Melissa. Thank you very much for this.

Could you perhaps add Fiscal Week? (please)

I know I may be pushing my (our) luck here… but could you possibly add Fiscal Week to your Power Query script? I have a solution in the form of a calculated column, but I’d really like to keep everything within one (your) script.

I’ve added “MonthInitial” and “DayOfWeekInitial”

(If you like it, feel free to add these columns to your script. I love the cleaner visuals)
– These columns sort correctly as I use zero-width invisible characters where there are duplicate values, eg “J” for Jan, June, July )

Script

script__with_MonthInitial_&_DayOfWeekInitial.txt (8.7 KB)
(this is Melissa’s script with renaming to suit me… as well as adding the two columns described above. It may be safer to just add these columns to her original script rather than use mine)

AntrikshSharma · February 8, 2021, 4:29pm

@michellepace Can you please share how you did that in DAX?

michellepace · February 8, 2021, 5:10pm

Hi Antriksh (@AntrikshSharma). It’s funny you ask that. I can’t claim the fame for coming up with this, but when I first saw it - - it was in DAX – but I needed to have it in SQL. Needless to say this now comes in all three flavors because I’ve converted the original Dax to both M-Code (see script above) as well as SQL - - see here for DAX and SQL: https://community.powerbi.com/t5/Desktop/Single-letter-month-abbreviations/m-p/462132.

AntrikshSharma · February 8, 2021, 5:26pm

@michellepace Haha, we all learn everyday, actually for some reason my visuals weren’t grouping and showed duplicate values, entered same code again and again and then it started working, thanks for sharing !

BrianJ · February 8, 2021, 5:31pm

@michellepace,

Love that invisible characters trick to address the duplicate name issue. Saw Ferrari do that in a video last year, and thought it was one of the coolest DAX tricks I’d ever seen.

Really like the way you’ve applied it to the single letter month names.

Brian

Melissa · February 8, 2021, 6:28pm

Hi @michellepace,

I’ve got solutions for the MonthInitial and DayOfWeekInitial columns.
I’ll look into the Fiscal Week requirement and get back to you, hopefully later today.

Melissa · February 8, 2021, 9:21pm

@michellepace

Added the Fiscal Week based on the logic you’ve provided, see this article, it requires a FYStartMonth and FYStartOfWeekDayName . Note that this can create partial weeks around the FY Start and End date. And another thing to note, that in the DAX code there’s a misalignment in FY this is sorted in the M code.

Also added MonthInitial and DayOfWeekInitial columns that use the the M function Character.FromNumber to add a unicode Zero-Width Space character if a letter isn’t distinct.

@BrianJ,
if you want to update the Cheat Sheet for Extended Date Table.
MonthInitial should be sorted by MonthOfYear and
DayOfWeekInitial sorted by DayOfWeek

BrianJ · February 8, 2021, 9:42pm

@Melissa,

Thanks – that’s fantastic. Biggest upgrade to the date table in a long time. I will do a major overhaul of the cheat sheet this week, and add the information for how to sort each column. Once done, I will post it and at that point I think doing a video to highlight both would be great.

Brian

michellepace · February 9, 2021, 5:03pm

Hi @Melissa and @BrianJ,

I think there may be a little mistake in the iso weeks. Sorry, I don’t mean to dig for mistakes. It’s the first time “iso weeks” has ever been important to me. I’ve just started doing some work for a company who speaks completely in “weeks like we see on our desk calendars” haha. Which is iso weeks.

I copied Melissa’s latest script into the attached pbix (with no changes at all). When you get a moment could you please have a quick look? Happy to be corrected but the below looks a little off. I added comments in the hope you’ll see a pattern and get a good idea of where the m-code error could be… my m-coding leaves much to be desired (still) .

Dates_IsoWeekCheck.pbix (113.4 KB)

Thanks once again for this!
Michelle

Melissa · February 9, 2021, 8:59pm

Hi @michellepace,

Thanks for your feedback, I appreciate it.
However I don’t see an issue with the ISO Weeks, so I suspect you’ve selected the “Year” field instead of the "ISO Year" field and that has gotten you into trouble…

Only the boundaries of the date range can cause a partial first- and or last week in the Date table. Let me know if that solves it for you.

Cameron · February 10, 2021, 4:20am

So it is the initial code that keeps getting updated in this thread with the revisions - I couldn’t see an edited flag but looked to be.

On the topic of the Day initials, a stand-alone Short Month Name - eg Jan, Feb is something that i often use. atm, it is concatenated in the Month & Year column.

Melissa · February 10, 2021, 8:46am

Hi @Cameron,

Yes to prevent you from having to search through a lengthy thread, to locate the latest version, I always update the initial post. So if you always copy the code from there you can take advantage of the latest modifications and/or updates as soon as they’ve become available.

Now also included the MonthShortName

.

@michellepace and @BrianJ,

Just added the sort by column FWeeknYear

BrianJ · February 10, 2021, 9:18am

@Melissa,

Thanks for the heads up. I was just working on the cheat sheet a few minutes ago, and will incorporate the new fields and sort fields tomorrow. Was going to ask Karen if she could work her graphic design magic on the cheat sheet, so that we’ll have a really nice, Enterprise DNA branded version.

Brian