I copied the following code from the net to automatically create a date table for further use. I also need the Quarter in the table so I added a line of code:
Quarter = Table.AddColumn(Year, "QuarterOfYear", each Date.QuarterOfYear([Date])),
The table creates fine, but without the "QuarterOfYear" Column. I'm sure I'm missing something.
Any helpful hints? Thanks.
complete script:
let
CreateDateTable = (StartDate, EndDate) =>
let
//StartDate=#date(2010,1,1),
//EndDate=#date(2014,12,31),
//Create lists of month and day names for use later on
MonthList = {"January", "February", "March", "April", "May", "June"
, "July", "August", "September", "October", "November", "December"},
DayList = {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"},
//Find the number of days between the end date and the start date
NumberOfDates = Duration.Days(EndDate-StartDate),
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
//Turn this list into a table
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}
, null, ExtraValues.Error),
//Caste the single column in the table to type date
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
//Add custom columns for day of month, month number, year
DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),
MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),
Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),
Quarter = Table.AddColumn(Year, "QuarterOfYear", each Date.QuarterOfYear([Date])),
DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),
//Since Power Query doesn’t have functions to return day or month names,
//use the lists created earlier for this
MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),
DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),
IsThisWeek = Table.AddColumn(DayName, "IsThisWeek", each Date.IsInCurrentWeek([Date])),
//Add a column that returns true if the date on rows is the current date
IsToday = Table.AddColumn(IsThisWeek, "IsToday", each Date.IsInCurrentDay([Date])),
IsThisYear = Table.AddColumn(IsToday, "IsThisYear", each Date.IsInCurrentYear([Date])),
WeekEnding = Table.AddColumn(IsThisYear , "Week Ending", each Date.EndOfWeek([Date])),
#"Removed Columns" = Table.RemoveColumns(WeekEnding,{"IsToday"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"DayOfMonth", Int64.Type}, {"MonthNumberOfYear", Int64.Type}, {"Year", Int64.Type}, {"DayOfWeekNumber", Int64.Type}})
in
#"Changed Type",
#"Aufgerufene FunktionCreateDateTable" = CreateDateTable(#date(2013, 1, 1), #date(2017, 12, 31))
in
#"Aufgerufene FunktionCreateDateTable"
