Hi @dillenbram,
I recently updated (my extended version of) the date table query and this includes FQ and FP.
So I hope you find this is helpful.
**UPDATE: For the most up to date version of the Date table M function, please refer to:
**UPDATE: added a ChangedType step at the end
let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number, optional Holidays as list ) as table =>
let
FYStartMonth = try if List.Contains( {1..12}, FYStartMonth ) then FYStartMonth else 1 otherwise 1,
CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, 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, "MonthName", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year]), type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date]), Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertMonthEnding = Table.AddColumn(InsertWeekEnding, "MonthEnding", each Date.EndOfMonth([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertMonthEnding, "Week Number", each //Date.WeekOfYear([Date]), type number),
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),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber, "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 FYStartMonth =1 then Text.End(Text.From([Year]), 2) else if [MonthOfYear]>=FYStartMonth then Text.From(Number.From(Text.End(Text.From([Year]), 2))+1) 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 [DayInWeek] = 5 then false else if [DayInWeek] = 6 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),
InsertWeekOffset = Table.AddColumn(InsertIsHoliday, "WeekOffset", each (Number.From(Date.StartOfWeek([Date]))-Number.From(Date.StartOfWeek(DateTime.LocalNow())))/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)),
CurrentFiscalYear = IdentifyCurrentDate{0}[Fiscal Year],
InsertFYoffset = Table.AddColumn(InsertYearOffset, "FiscalYearOffset", each Number.From(Text.Range([Fiscal Year],2,2)) - Number.From(Text.Range(CurrentFiscalYear,2,2)), type number),
InsertCompletedWeek = Table.AddColumn(InsertFYoffset, "CompletedWeek", each [WeekEnding] < Date.From(Date.EndOfWeek(CurrentDate)), type logical),
InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "CompletedMonth", each [MonthEnding] < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "CompletedQuarter", 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}, {"DayInWeek", Int64.Type}, {"Week Number", 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