Help creating date table that includes fiscal hierarchy

Hello I used PBi occasionaly over the last year and just finished a 4 week weekend training and now trying to build more robust reporting and dashbaords and one of my goals is to create a Date Table using script and include our fiscal calender years and quarters.

Searching out in Google we found on another community posting this blog post where the user built their own script http://geekswithblogs.net/darrengosbell/archive/2014/03/23/extending-the-powerquery-date-table-generator-to-include-iso-weeks.aspx

Below I put together a table for an example of 8 fiscal years of what our FY calendar dates are and we have tried modfiying the script below that was in the blog post but to the right in my table is what the results are coming out as. Other than changing Jan4 to Jan1 in the script which I have alrdeay tried I am not sure where else I need to edit. Any help to lead me in the rigth path would very much be appreacited!

Fiscal Year

• Starts on the Monday of the first week that contains 1/1
• 5-4-4 weeks in each quarter
• Every 6 years has 5th week in September

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>

let

DayCount = Duration.Days(Duration.From(EndDate - StartDate)),

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, "CALYear", each Date.Year([Date])),

InsertQuarter = Table.AddColumn(InsertYear, "CALQuarterOfYear", each Date.QuarterOfYear([Date])),

InsertMonth = Table.AddColumn(InsertQuarter, "CALMonthOfYear", each Date.Month([Date])),

InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),

InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [CALYear] * 10000 + [CALMonthOfYear] * 100 + [DayOfMonth]),

InsertMonthName = Table.AddColumn(InsertDayInt, "CALMonthName", each Date.ToText([Date], "MMMM", Culture), type text),

InsertMonthShort = Table.AddColumn(InsertMonthName, "CALMonthNameShort", each Date.ToText([Date], "MMM", Culture), type text),

InsertCalendarMonth = Table.AddColumn(InsertMonthShort, "CALMonthInCalendar", each (try(Text.Range([CALMonthName],0,3)) otherwise [CALMonthName]) & " " & Number.ToText([CALYear])),

InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "CALQuarterInCalendar", each "Q" & Number.ToText([CALQuarterOfYear]) & " " & Number.ToText([CALYear])),

InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),

InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),

InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),

InsertFyYear = Table.AddColumn(InsertWeekEnding, "zFyYearSort", each if [CALMonthOfYear] <= 6 then Date.Year([Date])-1 else Date.Year([Date])),

InsertFyYearCode  = Table.AddColumn(InsertFyYear , "FyYear", each Text.End(Text.From([zFyYearSort]),2)&"/"&Text.End(Text.From([zFyYearSort]+1),2),type text),

InsertFyQtr = Table.AddColumn(InsertFyYearCode  , "FyQuarter", each if [CALMonthOfYear] <= 6 then "Q"&Text.From(Date.QuarterOfYear([Date])+2) else "Q"&Text.From(Date.QuarterOfYear([Date])-2)),

InsertFyMonth = Table.AddColumn(InsertFyQtr , "FyMonth", each if [CALMonthOfYear] <= 6 then Date.Month([Date])+6 else Date.Month([Date])-6),

InsertFyYearQtr = Table.AddColumn(InsertFyMonth , "FyYearQtr", each [FyYear] & "-" & [FyQuarter], type text),

InsertFyYearMonth = Table.AddColumn(InsertFyYearQtr , "FyYearMonth", each [FyYear] & "-" & [CALMonthNameShort], type text),

InsertFyYearMonthNum = Table.AddColumn(InsertFyYearMonth, "FyYearMonthNum", each Text.From([zFyYearSort]) & "-" & Text.PadStart(Text.From([FyMonth]),2,"0"), type text),

InsertCurrentThursday = Table.AddColumn(InsertFyYearMonthNum , "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), type date),

InsertKratosWeekJan4 = Table.AddColumn(InsertCurrentThursday, "KratosWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),

InsertKratosWeekYear = Table.AddColumn(InsertKratosWeekJan4, "KratosYear", each Date.Year([CurrentThursday])) ,

InsertKratosWeekFirstMon = Table.AddColumn(InsertKratosWeekYear, "KratosWeekFirstMon", each

if [CurrentThursday] < [KratosWeekJan4]

then Date.AddDays([CurrentThursday],-3)

else Date.AddDays([KratosWeekJan4], - Date.DayOfWeek([KratosWeekJan4],1) )

,type date),

InsertKratosWeekNum = Table.AddColumn(InsertKratosWeekFirstMon, "KratosWeek", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [KratosWeekFirstMon]))+1) /7 )), type number),

InsertKratosWeekID = Table.AddColumn(InsertKratosWeekNum, "KratosWeekID", each [KratosYear] * 100 + [KratosWeek], type number),

InsertKratosWeekName = Table.AddColumn(InsertKratosWeekID, "KratosWeekName", each Text.From([KratosYear]) & "W" & Text.End( "0" & Text.From(([KratosWeek]*10) + [DayInWeek]),3)),

InsertKratosWeekNameLong = Table.AddColumn(InsertKratosWeekName, "KratosWeekNameLong", each Text.From([KratosYear]) & "-W" & Text.End( "0" & Text.From([KratosWeek]),2) & "-" & Text.From([DayInWeek])),

fnPeriod544a = (weekNum) => let

Periods =

{

{(x)=>x<6, [P=1,Q=1,M="Jan"]},

{(x)=>x<10, [P=2,Q=1,M="Feb"]},

{(x)=>x<14, [P=3,Q=1,M="Mar"]},

{(x)=>x<19, [P=4,Q=2,M="Apr"]},

{(x)=>x<23, [P=5,Q=2,M="May"]},

{(x)=>x<27, [P=6,Q=2,M="Jun"]},

{(x)=>x<32, [P=7,Q=3,M="Jul"]},

{(x)=>x<36, [P=8,Q=3,M="Aug"]},

{(x)=>x<40, [P=9,Q=3,M="Sep"]},

{(x)=>x<45, [P=10,Q=4,M="Oct"]},

{(x)=>x<49, [P=11,Q=4,M="Nov"]},

{(x)=>true, [P=12,Q=4,M="Dec"]}

},

Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}

in

Result,

InsertPeriod544 = Table.AddColumn(InsertKratosWeekNameLong, "Period544Record", each fnPeriod544a([KratosWeek])),

ExpandPeriod544 = Table.ExpandRecordColumn(InsertPeriod544, "Period544Record", {"P","Q","M" }, {"KratosMonth", "KratosQuarter", "KratosMonthName"}),

RemovedColumns = Table.RemoveColumns(ExpandPeriod544,{"CurrentThursday", "KratosWeekFirstMon", "KratosWeekJan4"}),

DaysFromToday = Table.AddColumn(RemovedColumns, "Days from Today" , each Date.From(DateTime.LocalNow()) - [Date]),

WeeksFromToday = Table.AddColumn(DaysFromToday, "Weeks from Today" , each (Date.From(DateTime.LocalNow()) - [Date])/7),

MonthsFromToday = Table.AddColumn(WeeksFromToday, "Months from Today" , each (Date.From(DateTime.LocalNow()) - [Date])/(365/12)),

ChangedType1 = Table.TransformColumnTypes(MonthsFromToday,{{"Date", type date}, {"CALYear", Int64.Type}, {"CALQuarterOfYear", Int64.Type}, {"Days from Today", Int64.Type}, {"Weeks from Today", Int64.Type}, {"Months from Today", Int64.Type}, {"CALMonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"CALMonthName", type text}, {"CALMonthNameShort", type text}, {"CALMonthInCalendar", type date}, {"CALQuarterInCalendar", type text}, {"DayInWeek", Int64.Type}, {"DayOfWeekName", type text}, {"WeekEndingFriday", type date}, {"zFyYearSort", Int64.Type},{"FyYear", type text}, {"FyQuarter", type text}, {"FyMonth", Int64.Type}, {"KratosYear", Int64.Type}, {"KratosWeek", Int64.Type}, {"KratosWeekID", Int64.Type}, {"KratosWeekName", type text}, {"KratosWeekNameLong", type text}, {"KratosMonth", Int64.Type}, {"KratosQuarter", Int64.Type}, {"KratosMonthName", type text}}),

InFuture = Table.AddColumn(ChangedType1 , "Date in Future" , each if Number.Sign([Days from Today]) = -1 then true else false, type logical),

ReorderedColumns = Table.ReorderColumns(InFuture ,{"Date", "CALYear", "CALQuarterOfYear", "CALMonthOfYear", "DayOfMonth", "DateInt", "CALMonthName", "CALMonthNameShort", "CALMonthInCalendar", "CALQuarterInCalendar", "DayInWeek", "DayOfWeekName", "WeekEndingFriday", "zFyYearSort", "FyYear", "FyQuarter", "FyMonth", "FyYearQtr", "FyYearMonth", "FyYearMonthNum", "KratosYear", "KratosMonth", "KratosQuarter", "KratosWeek", "KratosMonthName", "KratosWeekID", "KratosWeekName", "KratosWeekNameLong"})

in

ReorderedColumns ,

    #"Invoked FunctionCreateDateTable" = CreateDateTable(#date(1997, 01, 01), #date(2030, 12, 31), "en-US"),

    #"Renamed Columns" = Table.RenameColumns(#"Invoked FunctionCreateDateTable",{{"Date", "Date (connect dates in data model)"}, {"KratosMonth", "KratosPeriod"}})

in

    #"Renamed Columns"