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.
