Fiscal Table - Get Dates

Ok, this code i got off google a while back and i've made some modifications.  Create this as a blank query and call it fnDateTable

 

 

//Create Date Dimension
let
    Source = (StartDate as date, EndDate as date)=>
let
    //Capture the date range from the parameters
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
    Date.Day(StartDate)),
    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
    Date.Day(EndDate)),
//Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate),
//Take the count of dates and turn it into a list of dates
    GetDateList = List.Dates(StartDate, GetDateCount+1, 
    #duration(1,0,0,0)),
//Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
//Create various date attributes from the date column
    //Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year([Date])),
//Add Quarter Value Column
    QuarterValue = Table.AddColumn(YearNumber , "QuarterVal", 
    each Number.ToText(Date.QuarterOfYear([Date]))),
//Set as whole number
    SetQuarterType = Table.TransformColumnTypes(QuarterValue,{{"QuarterVal", Int64.Type}}),
//Add Quarter Column
    QuarterNumber = Table.AddColumn(SetQuarterType , "Quarter", 
    each "Q" & Number.ToText(if [QuarterVal] = 1 then 4 else [QuarterVal] - 1)),
//Remove Quarter Value Column
    RemoveQuarterValue = Table.RemoveColumns(QuarterNumber, {"QuarterVal"}),
//Add Week Number Column
    WeekNumber = Table.AddColumn(RemoveQuarterValue , "Week Number", 
    each Date.WeekOfYear([Date])),
//Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month([Date])),
//Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month", 
    each Date.ToText([Date],"MMMM")),
// Add FYear
 FYear = Table.AddColumn(MonthName, "FYear", each if [Month Number] <= 3 then Number.ToText(Number.FromText(Text.End(Number.ToText([Year]),2)) - 1) & "-" & Text.End(Number.ToText([Year]),2) else Text.End(Number.ToText([Year]),2) & "-" & Number.ToText(Number.FromText(Text.End(Number.ToText([Year]),2)) + 1)),
//Add Day of Week Column
    DayOfWeek = Table.AddColumn(FYear , "Day of Week", 
    each Date.ToText([Date],"dddd"))
in
    DayOfWeek
in
    Source

 

 

 

Next you need to invoke this function within another blank query, something like this:

let
    Source = fnDateTable(#date(2013, 1, 1), #date(Date.Year(DateTime.LocalNow()), 12, 31)),
    #"Set Field Types" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Year", Int64.Type}, {"Quarter", type text}, {"Week Number", Int64.Type}, {"Month Number", Int64.Type}, {"Month", type text}, {"Day of Week", type text}, {"FYear", type text}})
in
    #"Set Field Types"

You'll need to set what date range you want.  The above will do from 2013 until the end of the current year.