Extended Date Table (Power Query M function)

[Melissa]

Hi everyone,

To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know!

// August 2022
There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a record. That record includes the new v2, the deprecated v1.32 and a change log.

Copy the M code below for the new V2 and paste it into a new blank query.

.

let fnDateTable = ( StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional WDStartNum as number, optional AddRelativeNetWorkdays as logical ) as table =>
  let
      FYStartMonth = List.Select( {1..12}, each _ = FYStartMonthNum ){0}? ?? 1,
      WDStart = List.Select( {0..1}, each _ = WDStartNum ){0}? ?? 0,
      CurrentDate = Date.From( DateTime.FixedLocalNow()),
      DayCount = Duration.Days( Duration.From( EndDate - StartDate)) +1,
      weekStart = Day.Monday,
      Source = List.Dates( StartDate, DayCount, #duration(1,0,0,0)),
      AddToday = if List.Contains(Source, CurrentDate) then Source else List.Combine( {Source, {CurrentDate}}),
      ToTable = Table.FromList(AddToday, Splitter.SplitByNothing(), type table [Date = Date.Type] ),
      InsertYear = Table.AddColumn(ToTable, "Year", each Date.Year([Date]), type number),
      InsertYearOffset = Table.AddColumn(InsertYear, "CurrYearOffset", each Date.Year([Date]) - Date.Year( Date.From(CurrentDate)), type number),
      InsertCompletedYear = Table.AddColumn(InsertYearOffset, "YearCompleted", each Date.EndOfYear([Date]) < Date.From( Date.EndOfYear(CurrentDate)), type logical),

      InsertQuarterNum = Table.AddColumn(InsertCompletedYear, "Quarter Number", each Date.QuarterOfYear([Date]), type number),
      InsertQuarter = Table.AddColumn(InsertQuarterNum, "Quarter", each "Q" & Number.ToText([Quarter Number]), type text),
      InsertStartOfQuarter = Table.AddColumn(InsertQuarter, "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
      InsertEndOfQuarter = Table.AddColumn(InsertStartOfQuarter, "End of Quarter", each Date.EndOfQuarter([Date]), type date),
      InsertCalendarQtr = Table.AddColumn(InsertEndOfQuarter, "Quarter & Year", each "Q" & Number.ToText( Date.QuarterOfYear([Date])) & Date.ToText([Date], [Format = " yyyy"]), type text),
      InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", each [Year] * 10 + [Quarter Number], type number),
      InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "CurrQuarterOffset", each ((4 * Date.Year([Date])) +  Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) +  Date.QuarterOfYear(Date.From(CurrentDate))), type number),
      InsertCompletedQuarter = Table.AddColumn(InsertQuarterOffset, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),

      InsertMonth = Table.AddColumn(InsertCompletedQuarter, "Month", each Date.Month([Date]), type number),
      InsertStartOfMonth = Table.AddColumn(InsertMonth, "Start of Month", each Date.StartOfMonth([Date]), type date),
      InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "End of Month", each Date.EndOfMonth([Date]), type date),
      InsertCalendarMonth = Table.AddColumn(InsertEndOfMonth, "Month & Year", each Text.Proper( Date.ToText([Date], [Format = "MMM yyyy"])), type text),
      InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", each [Year] * 100 + [Month], type number),
      InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "CurrMonthOffset", each ((12 * Date.Year([Date])) +  Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) +  Date.Month(Date.From(CurrentDate))), type number),
      InsertCompletedMonth = Table.AddColumn(InsertMonthOffset, "MonthCompleted", each Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
      InsertMonthName = Table.AddColumn(InsertCompletedMonth, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text),
      InsertMonthShort = Table.AddColumn( InsertMonthName, "Month Short", each Text.Proper( Date.ToText([Date], "MMM")), type text),
      InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", each Text.Start([Month Name], 1) & Text.Repeat( Character.FromNumber(8203), Date.Month([Date]) ), type text),
      InsertDayOfMonth = Table.AddColumn(InsertMonthInitial, "Day of Month", each Date.Day([Date]), type number),
  
      InsertWeekNumber = Table.AddColumn(InsertDayOfMonth, "Week Number", each
        if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], weekStart)+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), weekStart)+1)+10)/7)
        else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], weekStart)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), weekStart)+1<4))
        then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], weekStart)+1)+10)/7), type number),
      InsertStartOfWeek = Table.AddColumn(InsertWeekNumber, "Start of Week", each Date.StartOfWeek([Date], weekStart), type date),
      InsertWeekEnding = Table.AddColumn(InsertStartOfWeek, "End of Week", each Date.EndOfWeek( [Date], weekStart), type date),
      InsertCalendarWk = Table.AddColumn(InsertWeekEnding, "Week & Year", each "W" & Text.PadStart( Text.From( [Week Number] ), 2, "0") & " " & Text.From(Date.Year( Date.AddDays( Date.StartOfWeek([Date], weekStart), 3 ))), type text ),
      InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], weekStart), 3 )) * 100 + [Week Number],  Int64.Type),
      InsertWeekOffset = Table.AddColumn(InsertWeeknYear, "CurrWeekOffset", each (Number.From(Date.StartOfWeek([Date], weekStart))-Number.From(Date.StartOfWeek(CurrentDate, weekStart)))/7, type number),
      InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", each Date.EndOfWeek( [Date], weekStart) < Date.From(Date.EndOfWeek(CurrentDate, weekStart)), type logical),
  
      InsertDayWeek = Table.AddColumn(InsertCompletedWeek, "Day of Week Number", each Date.DayOfWeek([Date], weekStart) + WDStart, Int64.Type),
      InsertDayName = Table.AddColumn(InsertDayWeek, "Day of Week Name", each Text.Proper( Date.ToText([Date], "dddd" )), type text),
      InsertDayInitial = Table.AddColumn(InsertDayName, "Day of Week Initial", each Text.Proper(Text.Start([Day of Week Name], 1)) & Text.Repeat( Character.FromNumber(8203), Date.DayOfWeek([Date], weekStart) + WDStart ), type text),
      InsertDayOfYear = Table.AddColumn(InsertDayInitial, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
      InsertDayInt = Table.AddColumn(InsertDayOfYear, "DateInt", each [Year] * 10000 + [Month] * 100 + [Day of Month], type number),
      InsertDayOffset = Table.AddColumn(InsertDayInt, "CurrDayOffset", each Number.From([Date]) - Number.From(CurrentDate), type number),
      InsertIsAfterToday = Table.AddColumn(InsertDayOffset, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
      InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWeekDay", each if Date.DayOfWeek([Date], weekStart) > 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 [IsWeekDay] = true and [IsHoliday] <> true then true else false, type logical),
      InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWeekDay] = false then "Weekend" else if [IsWeekDay] = true then "Weekday" else null, type text),

      InsertISOYear = Table.AddColumn( InsertDayType, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], weekStart), 3 )), type number),
      InsertISOqNum = Table.AddColumn(InsertISOYear, "ISO Quarter Number", each if [Week Number] >39 then 4 else if [Week Number] >26 then 3 else if [Week Number] >13 then 2 else 1, Int64.Type),
      InsertISOqtr = Table.AddColumn(InsertISOqNum, "ISO Quarter", each "Q" & Number.ToText([ISO Quarter Number]), type text),
      InsertISOQuarter = Table.AddColumn(InsertISOqtr, "ISO Quarter & Year", each "Q" & Number.ToText([ISO Quarter Number]) & " " & Number.ToText([ISO Year]), type text),
      InsertISOqNy = Table.AddColumn(InsertISOQuarter, "ISO QuarternYear", each [ISO Year] * 10 + [ISO Quarter Number], type number),

      // BufferTable = Table.Buffer(Table.Distinct( InsertISOqNy[[ISO Year], [DateInt]])),
      // InsertISOday = Table.AddColumn(InsertISOqNy, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])),  Int64.Type),
      AddFY = Table.AddColumn(InsertISOqNy, "Fiscal Year", each "FY" & (if [Month] >= FYStartMonth and FYStartMonth >1 then Text.From([Year] +1) else Text.From([Year])), type text),
      //AddFYs = Table.AddColumn(AddFY, "Fiscal Year short", each "FY" & (if [Month] >= FYStartMonth and FYStartMonth >1 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 )) & " " & (if [Month] >= FYStartMonth and FYStartMonth >1 then Text.From([Year] +1) else Text.From([Year])), type text),
      AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each (if [Month] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ), type number),
      AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period Number", each if [Month] >= FYStartMonth and FYStartMonth >1 then [Month] - (FYStartMonth-1) else if [Month] >= FYStartMonth and FYStartMonth =1 then [Month] else [Month] + (12-FYStartMonth+1), type number),
      AddFP = Table.AddColumn(AddFM, "Fiscal Period", each "FP" & Text.PadStart( Text.From([Fiscal Period Number]), 2, "0") & " " & (if [Month] >= FYStartMonth and FYStartMonth >1 then Text.From([Year] +1) else Text.From([Year])), type text),
      AddFMnYr = Table.AddColumn(AddFP , "FPeriodnYear", each (if [Month] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 100 + [Fiscal Period Number], type number),
      FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
      InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", each if [Month] >= FYStartMonth and FYStartMonth >1 then #date( Date.Year([Date])+1, FYStartMonth, 1) else #date( Date.Year([Date]), FYStartMonth, 1), type date ),

      InitTable = Table.FromList( List.Transform( {Number.From(FYCalendarStart) .. Number.From(EndDate)}, Date.From), Splitter.SplitByNothing(), type table [DateFW = Date.Type]),
      AddFFD = Table.AddColumn( InitTable, "FiscalFirstDay", each if Date.Month([DateFW]) < FYStartMonth then #date(Date.Year([DateFW]), FYStartMonth, 1) else #date(Date.Year([DateFW]) + 1, FYStartMonth, 1)),
      AddFWSD = Table.AddColumn( AddFFD, "FWStartDate", each Date.AddYears(Date.StartOfWeek([DateFW], weekStart), 1)),
      Group1 = Table.Group( AddFWSD, {"FiscalFirstDay", "FWStartDate"}, {{"AllRows", each _, type table [DateFW = nullable date, FiscalFirstDay = date, FWStartDate = date]}}),
      Group2 = Table.Group( Group1, {"FiscalFirstDay"}, {{"AllRows2", each _, type table [FiscalFirstDay = date, FWStartDate = date, AllRows = table]}}),
      AddIndex = Table.AddColumn( Group2, "Custom", each Table.AddIndexColumn([AllRows2], "Fiscal Week Number", 1, 1) )[[Custom]],
      ExpandG2 = Table.ExpandTableColumn( AddIndex, "Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "Fiscal Week Number"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "Fiscal Week Number"}), 
      ExpandG1 = Table.ExpandTableColumn( ExpandG2, "AllRows", {"DateFW"}, {"DateFW"} )[[DateFW], [Fiscal Week Number]],
      MergeFYW = Table.Join( InsertFFD, {"Date"}, ExpandG1, {"DateFW"}, JoinKind.LeftOuter, JoinAlgorithm.SortMerge ),
      FWlogic = List.Contains( {null}, FYStartMonthNum),
      UpdateFYWeek = if FWlogic then Table.ReplaceValue(MergeFYW, each [Fiscal Week Number], each if FYStartMonth =1 then [Week Number] else [Fiscal Week Number], Replacer.ReplaceValue, {"Fiscal Week Number"}) else MergeFYW,
      AddFYW = Table.AddColumn( UpdateFYWeek, "Fiscal Week", each if FWlogic then "F" & [#"Week & Year"] else if FYStartMonth =1 then "FW" & Text.PadStart( Text.From([Fiscal Week Number]), 2, "0") & Date.ToText([Date], " yyyy") else if Date.Month([Date]) < FYStartMonth then "FW" & Text.PadStart( Text.From([Fiscal Week Number]), 2, "0") & Date.ToText([Date], " yyyy") else "FW" & Text.PadStart(Text.From([Fiscal Week Number]), 2, "0") & " " & Text.From( Date.Year([Date])+1), type text),
      InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each if FWlogic then [WeeknYear] else (if FYStartMonth =1 then Date.Year([Date]) else if Date.Month([Date]) < FYStartMonth then Date.Year([Date]) else Date.Year([Date])+1) * 100 + [Fiscal Week Number],  Int64.Type),
      
      CurrentDateRecord = Table.SelectRows(InsertFWeeknYear, each ([Date] = CurrentDate)),
      CurrentISOyear = CurrentDateRecord{0}[ISO Year],
      CurrentISOqtr = CurrentDateRecord{0}[ISO Quarter Number],
      CurrentYear = CurrentDateRecord{0}[Year],
      CurrentMonth = CurrentDateRecord{0}[Month],
      CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
      PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
      CurrentFY = CurrentDateRecord{0}[Fiscal Year],
      CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
      CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
      CurrentFW = CurrentDateRecord{0}[FWeeknYear],

      InsertISOYrOffset = Table.AddColumn(InsertFWeeknYear, "ISO CurrYearOffset", each [ISO Year] - CurrentISOyear, type number),
      InsertISOQtrOffset = Table.AddColumn(InsertISOYrOffset, "ISO CurrQuarterOffset", each ((4 * [ISO Year]) +  [ISO Quarter Number]) - ((4 * CurrentISOyear) + CurrentISOqtr), type number),
      InsertFYoffset = Table.AddColumn(InsertISOQtrOffset, "Fiscal CurrYearOffset", each try (if [Month] >= FYStartMonth then [Year]+1 else [Year]) - (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
      InsertCurrentFY = Table.AddColumn(InsertFYoffset, "IsCurrentFY", each if [Fiscal Year] = CurrentFY then true else false, type logical),
      InsertCurrentFQ = Table.AddColumn(InsertCurrentFY, "IsCurrentFQ", each if [FQuarternYear] = CurrentFQ then true else false, type logical),
      InsertCurrentFP = Table.AddColumn(InsertCurrentFQ, "IsCurrentFP", each if [FPeriodnYear] = CurrentFP then true else false, type logical),
      InsertCurrentFW = Table.AddColumn(InsertCurrentFP, "IsCurrentFW", each if [FWeeknYear] = CurrentFW then true else false, type logical),
      InsertPYTD = Table.AddColumn(InsertCurrentFW, "IsPYTD", each if CurrentYear-1 = [Year] and [Day of Year] <= CurrentDateRecord{0}[Day of Year] then true else false, type logical),
        ListPrevFYDates = List.Buffer( Table.SelectRows( Table.ExpandTableColumn( Table.NestedJoin(
            Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( PrevFiscalFirstDay, Number.From(CurrentFiscalFirstDay-PrevFiscalFirstDay),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1), {"Index"}, 
            Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( Date.AddYears( PrevFiscalFirstDay, -1), Number.From( PrevFiscalFirstDay - Date.AddYears( PrevFiscalFirstDay, -1)),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1)
            , {"Index"}, "Table", JoinKind.LeftOuter), "Table", {"DateFY"}, {"PrevDateFY"}), each [DateFY] <= CurrentDate)[PrevDateFY] ),
      InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", each if [Fiscal CurrYearOffset] = -1 and List.Contains(ListPrevFYDates, [Date] ) then true else false, type logical),
      InsertNetWorkdays = if AddRelativeNetWorkdays = true then Table.AddColumn(InsertPFYTD, "Relative Networkdays", each fxNETWORKDAYS( StartDate, [Date], Holidays ), type number ) else InsertPFYTD,
      fxNETWORKDAYS = (StartDate, EndDate, optional Holidays as list) =>
        let
          ListOfDates = List.Dates( StartDate, Number.From(EndDate-StartDate)+1, Duration.From(1) ),
          DeleteHolidays = if Holidays = null then ListOfDates else List.Difference( ListOfDates, List.Transform(Holidays, Date.From )),
          DeleteWeekends = List.Select( DeleteHolidays, each Date.DayOfWeek( _, weekStart) < 5 ),
          CountDays = List.Count( DeleteWeekends)
        in
          CountDays,
      RemoveToday = Table.RemoveColumns( if not List.Contains(Source, CurrentDate) then Table.SelectRows(InsertNetWorkdays, each ([Date] <> CurrentDate)) else InsertNetWorkdays, {"Day of Year", "FiscalFirstDay"}), 
      ChType = Table.TransformColumnTypes(RemoveToday,{{"Year", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month", Int64.Type}, {"Day of Month", Int64.Type}, {"DateInt", Int64.Type}, {"Day of Week Number", Int64.Type}, {"ISO CurrYearOffset", Int64.Type}, {"ISO QuarternYear", Int64.Type}, {"ISO CurrQuarterOffset", Int64.Type}, {"Week Number", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"FQuarternYear", Int64.Type}, {"Fiscal Period Number", Int64.Type}, {"FPeriodnYear", Int64.Type}, {"CurrWeekOffset", Int64.Type}, {"CurrMonthOffset", Int64.Type}, {"CurrQuarterOffset", Int64.Type}, {"CurrYearOffset", Int64.Type}, {"Fiscal CurrYearOffset", Int64.Type}, {"Fiscal Week Number", Int64.Type}}),
      ReorderCols = Table.ReorderColumns(ChType,{"Date", "Year", "CurrYearOffset", "YearCompleted", "Quarter Number", "Quarter", "Start of Quarter", "End of Quarter", "Quarter & Year", "QuarternYear", "CurrQuarterOffset", "QuarterCompleted", "Month", "Start of Month", "End of Month", "Month & Year", "MonthnYear", "CurrMonthOffset", "MonthCompleted", "Month Name", "Month Short", "Month Initial", "Day of Month", "Week Number", "Start of Week", "End of Week", "Week & Year", "WeeknYear", "CurrWeekOffset", "WeekCompleted", "Day of Week Number", "Day of Week Name", "Day of Week Initial", "DateInt", "CurrDayOffset", "IsAfterToday", "IsWeekDay", "IsHoliday", "IsBusinessDay", "Day Type", "ISO Year", "ISO CurrYearOffset", "ISO Quarter Number", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO CurrQuarterOffset", "Fiscal Year", "Fiscal CurrYearOffset", "Fiscal Quarter", "FQuarternYear", "Fiscal Period Number", "Fiscal Period", "FPeriodnYear", "DateFW", "Fiscal Week Number", "Fiscal Week", "FWeeknYear", "IsCurrentFY", "IsCurrentFQ", "IsCurrentFP", "IsCurrentFW", "IsPYTD", "IsPFYTD"}),
      ListCols = if FWlogic then Table.RemoveColumns(ReorderCols,{"ISO Quarter Number", "Fiscal Year", "Fiscal Quarter", "FQuarternYear", "Fiscal Period Number", "Fiscal Period", "FPeriodnYear", "DateFW", "Fiscal Week Number", "Fiscal Week", "FWeeknYear", "Fiscal CurrYearOffset", "IsCurrentFQ", "IsCurrentFP", "IsCurrentFW"}) else Table.RemoveColumns(ReorderCols,{"Fiscal Period Number", "DateFW", "Fiscal Week Number", "ISO Quarter Number"})
  in
      ListCols,
      Documentation = [
      Documentation.Name =  " fxCalendar", 
      Documentation.Description = " Date table function to create an ISO-8601 calendar", 
      Documentation.LongDescription = " Date table function to create an ISO-8601 calendar", 
      Documentation.Category = " Table", 
      Documentation.Version = " 2.03: IsCurrentFW fix",
      Documentation.Source = " local", 
      Documentation.Author = " Melissa de Korte", 
      Documentation.Examples = { [Description =  " See: https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390", 
        Code = " Optional paramters: #(lf)
        (FYStartMonthNum) Month number the fiscal year starts, Januari if omitted #(lf) 
        (Holidays) Select a query (and column) that contains a list of holiday dates #(lf) 
        (WDStartNum) Switch default weekday numbering from 0-6 to 1-7 by entering a 1 #(lf)
        (AddRelativeNetWorkdays) if true adds a Relative Networkdays column to the date table #(lf)
        #(lf)
        Important to note: #(lf)
        [Fiscal Week] starts on a Monday and can contain less than 7 days in a First- and/or Last Week of a FY #(lf)
        [IsWeekDay] does not take holiday dates into account  #(lf)
        [IsBusinessDay] does take optional holiday dates into account  #(lf)
        [IsPYTD] and [IsPFYTD] compare Previous [Day of Year] with the Current [Day of Year] number, so dates don't align in leap years #(lf)
        IMPORTANT! No Fiscal columns will be added if the (FYStartMonthNum) is omitted", 
        Result = " " ] }
      ]
  in 
  Value.ReplaceType( fnDateTable, Value.ReplaceMetadata( Value.Type( fnDateTable ), Documentation ))

.
Don’t want to invoke a function?
With the minor changes depicted below the output of the M code will be a table.

.
How to make a date table fully dynamic?

Creating a dynamic Start-/EndDate for the Date table

Hi everyone, To make it easier to find here are some Power Query techniques to make the Date table Startdate and/or Enddate dynamic. If you want to give that a try, go to Power Query, select New Source, Blank Query, open the Advanced Editor, paste in this Date table M code and Invoke that function. A new query will appear in the Queries pane with the name ‘Invoked Function’ in the formula bar of that query’s Source step you can distinguish the following elements. Date table function query na…

.
To many columns for your bussiness requirement?
By adding a single step you can select only those columns that are of intrest to you. In this video I’ll walk you throught how to do just that.

Extended Date Table Power Query M Function - Updated

.
But wait, how do I sort that column?
@BrianJ has created a Cheat Sheet for this date table, it lists an example value, the data type and appropriate sort by columns. You can find that here.

New Cheat Sheet for Extended Date Table

Extended Date Table Fans, I’m pleased to announce the release of the new deluxe version of the Extended Date Table Cheat Sheet, an essential accompaniment to @Melissa’s awesome and recently revised Extended Date Table code. Click here to obtain the new version, which now includes not only all the field names and examples for each, but field type and information on how to sort every field in the table. We will update this with every major update @Melissa makes to the date table code. We hope…

.
Want to quickly change Date format and Summarize By with a script in TE (Tabular Editor)?
This script requires a date table with the name Dates if you call yours something else, that’s fine of course, just update the name between double quotes in the first variable.
Make other changes according to your personal preference.

var dateTableName = "Dates";
var dateTable = Model.Tables.FirstOrDefault(t => t.Name == dateTableName);
if (dateTable != null)
{
    foreach (var c in dateTable.Columns)
    {
        if (c.DataType == DataType.Double || c.DataType == DataType.Int64)
        {
            c.SummarizeBy = AggregateFunction.None;
            c.FormatString = "0";  // Format as integer
        }
        else if (c.DataType == DataType.DateTime)
        {
            c.FormatString = "Short Date";  // Use short date format
        }
        // Add more conditions here if there are other DataTypes to handle
    }
}

.
Want to create your own Date table?
Keep these requirements in mind for Time Intelligence functions to work properly:

• Must have a column of data type date (or date/time )—known as the date column
• The date column must contain unique values
• The date column must not contain BLANKs
• The date column must not have any missing dates
• The date column must span full years. Where a year isn’t necessarily a calendar year (January-December). And cover all dates from the earliest- to the last date present in your dataset.
• The date table must be marked as a date table

.

You can let me know you’ve appreciated this post by giving it a

Note. If you always copy the latest version of the M code above, you can take advantage of modifications and/or updates as soon as they become available…

[Melissa]

Just adding some other search terms: power query calendar table, calendar function

[Whitewater100]

Hi Melissa:

This really cool and thoughtful of you for sharing!

Best regards,

Bill S

[NajahS]

Thanks @Melissa, you are awesome
Have a beautiful day
Najah

[sam.mckay]

Agree just amazing work.

[DavidNealon]

Any chance we can get a separate Extended Time Table seeing we are suggested to split out time from date. Would be great if we could get an Extended Time Table to go with it.

[Melissa]

Hi David,

Makes sense, you can find it here:

Time Table (Power Query M function)

Hi everyone, @DavidNealon thought it would be a nice addition if there was a Time Table Power Query M code function available in this category as well. Now I can’t take any credit because it was designed by Gina Meronek, you can find it and the full article she wrote here. When invoked the function creates a Time Table at a granularity of Minutes. If your requirement is different that can easily be fixed. Note that other changes might be required as well but to give you some ideas: For Hourl…

[Walter]

Thank you again Melissa!

[pedroccamara]

Very good Melissa. I’ve just voted for a new virtual pbi training about all this. I really hope you guys manage to get some time to do it.
Thank you so much

[Melissa]

Hi Pedro,

As a matter of fact @BrianJ and I are collaborating on a Time Intelligence series as we speak and of course we will also be covering the Date table. So if you haven’t already, subscribe to the Enterprise DNA YouTube channel so you won’t miss a thing!

[pedroccamara]

oooohhhh that is soooooooo cooooll!!! and yes i am a dna youtube subscriber for quite a while now. And it’s a great idea to teach also about TI measures concerning last week or weeks or month or quarter regarding the beginning of the year.
Thanks a lot Melissa. That is great news!!!

[delaunoy]

Muchas gracias Melisa, Awesome

[Mohammed_ali]

This is very helpful Melisa, you are a rockstar here.

[kjssdca]

@Melissa I need to adjust the ISO Year column to match our “week-years.”
Our weeks run Sunday - Saturday, and our billing “months” end on the last Saturday in the calendar month.
I have been trying to adjust the code in the InsertWeekNumber= line to match this calendar. Can you explain out the last ‘else’ line in that code?
else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Sunday)+1)+10)/7), type number),
Specifically - Why is it adding 1, then 10 before dividing by 7?
Learning a ton here. . .
Thank you.

[Melissa]

Hi @kjssdca,

Thanks for your question.

By default the Power Query Date.DayOfWeek function returns a number between 0 and 6 starting on Sunday. To return the weekday number according to ISO8601 rules, you have to add the optional first-weekday value (= Day.Monday) and change the numbering so it runs between 1 and 7.
This explains the +1

The +10 / 7 is part of the algorithm for calculating the week number, you can find more on that here.

If you need assistance creating a customized week number, please create a new topic. You’ll find that there’s an increasing number of active members here who’d like to help out.

[Hesham]

Thank you @Melissa for sharing! Date Table is getting better and better

Your code has opened my appetite to keep the “EndDate” dynamic, so it will always be the last day of the current year. That would save me from updating/maintaining the Dates table in all my reports next year(unless there is another way which I am not aware of )

Using your M code, I have:
1- Removed “EndDate as date” from the first line and;
2- Added another line “EndDate = Date.EndOfYear(CurrentDate),” after the “Current Date”.

[Melissa]

Hi @Hesham,

Within this category you can also find a topic on creating a dynamic Start- and Enddate.

Creating a dynamic Start-/EndDate for the Date table

Hi everyone, To make it easier to find here are some Power Query techniques to make the Date table Startdate and/or Enddate dynamic. If you want to give that a try, go to Power Query, select New Source, Blank Query, open the Advanced Editor, paste in this Date table M code and Invoke that function. A new query will appear in the Queries pane with the name ‘Invoked Function’ in the formula bar of that query’s Source step you can distinguish the following elements. Date table function query na…

.
In addition there will also be a video on that topic in the Time Intelligence series Brian and I are doing. We’ll add a link here as soon as it’s released to the Enterprise DNA Channel.

Time Intelligence series by BrianJ and Melissa

Hi everyone, We’ve brought the Time Intelligence series we did for Enterprise DNA TV together in this post so it’ll be easier to find and navigate through completely. @BrianJ and I had great fun doing these. If you have any questions or concerns about the content covered in these video’s please create a new topic, refer to the video and @mention BrianJ or Melissa. But we’d also welcome your feedback within this thread of course. – THANK YOU – How to create a Holiday table [Ho…

.
Thanks for sharing your method

[Yeriel]

Thanks @Melissa!!!

This post came just right in time!!

[Paul]

Hi Melissa,
The optional parameters work fine, would it be possible to add a selection parameter to show Monday as Weekday 1 please, so users can select either 0 or 1.
Paul

[Melissa]

Hi Paul,

Thanks for your question!

That sounds like a nice addition indeed. So added the optional WDStartNum parameter this allows you to set the [DayOfWeek] numbering from either 0-6 or 1-7. However if omitted, the standard numbering will be 0-6.

Keep in mind that this doesn’t affect the start of the week day, that will always remain Monday as is custom to this Calendar and the ISO week logic.