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", "Kuupäev"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Aasta nr", each Date.Year([Kuupäev]), type number),
InsertQuarter = Table.AddColumn(InsertYear, "Kvartali nr", each Date.QuarterOfYear([Kuupäev]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, " Kv-aasta ", each "Q" & Number.ToText([Kvartali nr]) & " " & Number.ToText([Aasta nr]),type text),
InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, " Kv-aasta järjestus", each [Aasta nr] * 10 + [Kvartali nr], type number),
InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Kuu nr", each Date.Month([Kuupäev]), type number),
InsertMonthName = Table.AddColumn(InsertMonth, "Kuu nimi", each Date.ToText([Kuupäev], "MMMM", Culture), type text),
InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Kuu", each Date.ToText([Kuupäev], "MMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Kuu-aasta", each (try(Text.Range([Kuu nimi],0,3)) otherwise [Kuu nimi]) & " " & Number.ToText([Aasta nr]), type text),
InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Kuu-aasta järjestus", each [Aasta nr] * 100 + [Kuu nr], type number),
InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Nädala nr", each Date.WeekOfYear([Kuupäev]), type number),
InsertCalendarWk = Table.AddColumn(InsertWeek, "Nädal-aasta", each "W" & Number.ToText([Nädala nr]) & " " & Number.ToText([Aasta nr]), type text),
InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Nädal-aasta järjestus", each [Aasta nr] * 100 + [Nädala nr], type number),
InsertWeekEnding = Table.AddColumn(InsertCalendarWkOrder, "Nädala lõpp", each Date.EndOfWeek([Kuupäev]), type date),
InsertDay = Table.AddColumn(InsertWeekEnding, "Päeva nr", each Date.Day([Kuupäev]), type number),
InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Aasta nr] * 10000 + [Kuu nr] * 100 + [Päeva nr], type number),
InsertDayWeek = Table.AddColumn(InsertDayInt, "Nädalapäeva nr", each Date.DayOfWeek([Kuupäev]) + 1, type number),
InsertDayName = Table.AddColumn(InsertDayWeek, "Nädalapäev", each Date.ToText([Kuupäev], "dddd", Culture), type text),
InsertWeekend = Table.AddColumn(InsertDayName, "Nädalavahetus?", each if [Nädalapäeva nr] = 1 then "JAH" else if [Nädalapäeva nr] = 7 then "JAH" else "EI", type text),
InsertDayNameShort = Table.AddColumn(InsertWeekend, "Nädalap.", each Date.ToText([Kuupäev], "ddd", Culture), type text),
InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1),
InsertDayOfYear = Table.AddColumn(InsertIndex, "Päev aastas", each Date.DayOfYear([Kuupäev]), type number),
InsertCurrentDay = Table.AddColumn(InsertDayOfYear, "Täna?", each Date.IsInCurrentDay([Kuupäev]), type logical),
InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "See nädal?", each Date.IsInCurrentWeek([Kuupäev]), type logical),
InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "See kuu?", each Date.IsInCurrentMonth([Kuupäev]), type logical),
InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "See kvartal?", each Date.IsInCurrentQuarter([Kuupäev]), type logical),
InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "See aasta?", each Date.IsInCurrentYear([Kuupäev]), type logical),
InsertCompletedDay = Table.AddColumn(InsertCurrentYear, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Kuupäev] then "Y" else "N", type text),
InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Kuupäev])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Kuupäev])) then "N" else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Kuupäev])) then "Y" else "N", type text),
InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Kuupäev])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Kuupäev])) then "N" else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Kuupäev])) then "Y" else "N", type text),
InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Kuupäev])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Kuupäev])) then "N" else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Kuupäev])) then "Y" else "N", type text),
InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Kuupäev])) then "Y" else "N", type text),
InsertISOWeekNo = Table.AddColumn(InsertCompletedYear, "ISO Nädala nr", each if Number.RoundDown((Date.DayOfYear([Kuupäev])-(Date.DayOfWeek([Kuupäev], Day.Monday)+1)+10)/7)=0 then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Kuupäev])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Kuupäev])-1,12,31), Day.Monday)+1)+10)/7) else if (Number.RoundDown((Date.DayOfYear([Kuupäev])-(Date.DayOfWeek([Kuupäev], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Kuupäev]),12,31), Day.Monday)+1<4)) then 1 else Number.RoundDown((Date.DayOfYear([Kuupäev])-(Date.DayOfWeek([Kuupäev], Day.Monday)+1)+10)/7), type number),
InsertISOWeek = Table.AddColumn(InsertISOWeekNo, "ISO Nädal", each "W" & Number.ToText([ISO Nädala nr]), type text),
InsertDateToday = Table.AddColumn(InsertISOWeekNo, "Tänane kuupäev", each DateTime.LocalNow(), type date),
ChangedType1 = Table.TransformColumnTypes(InsertDateToday,{{"Tänane kuupäev", type date}})
in
ChangedType1
in
CreateDateTable