Created
July 2, 2019 22:14
-
-
Save aabundez/06f6d3517631ed9e6ad05e8c00e79ba9 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table => | |
let | |
// Determine number of days between start and end date | |
DayCount = Duration.Days(Duration.From(EndDate - StartDate)), | |
// Add a day back, as Duration math above will give you the days literally between rather than inclusive. Ex Dec 31 - Dec 1 = 30 days, but we need 31 days in that period | |
DayCountAdd = DayCount + 1, | |
// Create a Dates list object starting at the start date, for the duration of the day count, increment of one year | |
Source = List.Dates(StartDate,DayCountAdd,#duration(1,0,0,0)), | |
// Turn that date list into a table | |
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), | |
// Update the type to Date | |
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), | |
// Rename the only column in the table to date | |
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), | |
// Insert columns for different units within the date dimension and supporting columns for sorting | |
// Optional Culture parameter is used where applicable | |
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])), | |
InsertQuarter = Table.AddColumn(InsertYear, "QuarterNumber", each Date.QuarterOfYear([Date])), | |
InsertMonth = Table.AddColumn(InsertQuarter, "MonthNumber", each Date.Month([Date])), | |
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])), | |
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthNumber] * 100 + [DayOfMonth]), | |
InsertMonthName = Table.AddColumn(InsertDayInt, "Month", each Date.ToText([Date], "MMMM", Culture), type text), | |
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month Of Year", each (try(Text.Range([Month],0,3)) otherwise [Month]) & " " & Number.ToText([Year])), | |
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter of Year", each "Q" & Number.ToText([QuarterNumber]) & " " & Number.ToText([Year])), | |
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])), | |
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Of Week Name", each Date.ToText([Date], "dddd", Culture), type text), | |
InsertWeekStarting = Table.AddColumn(InsertDayName, "Week Starting", each Date.StartOfWeek([Date]), type date), | |
InsertWeekEnding = Table.AddColumn(InsertWeekStarting, "Week Ending", each Date.EndOfWeek([Date]), type date), | |
InsertMonthYrSort = Table.AddColumn(InsertWeekEnding, "MonthYrSort", each Date.ToText([Date], "yyyyMM", Culture), type number), | |
InsertQuarterSort = Table.AddColumn(InsertMonthYrSort, "QuarterYrSort", each Number.ToText([Year]) & Number.ToText([QuarterNumber])), | |
ChangeTypes2 = Table.TransformColumnTypes(InsertQuarterSort, {{"MonthYrSort", type number},{"QuarterYrSort", type number}}), | |
InsertWeekOfYr = Table.AddColumn(ChangeTypes2, "Week", each "W" & Number.ToText(Date.WeekOfYear([Date])), type text) | |
in | |
InsertWeekOfYr | |
in | |
CreateDateTable |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
My go-to Date table function