Create a Date Table will all essentials for a PowerBI Analytic project

[madhumnair]

HI there i like to share a date table created which can be resued to all your PowerBI analytic project.

let
Source = List.Dates( #"Startdate", Duration.Days( Enddate - #"Startdate" ) + 1,
#duration(1, 0, 0, 0)
),
#"Expanded Dates" = Table.FromList(
Source,
Splitter.SplitByNothing(),
{ "Dates" }, // Adds Column Name
null,
ExtraValues.Error
),
WEEKNUMBER = Table.AddColumn(#"Expanded Dates", "WeekNo", each Date.WeekOfYear([Dates])),
WEEKNAME = Table.AddColumn(WEEKNUMBER, "Weekname", each Date.DayOfWeekName([Dates])),
WEEKDAYNUMBER = Table.AddColumn(WEEKNAME, "Weekdayno", each Date.DayOfWeek([Dates])),
MONTHSHORTNAME = Table.AddColumn(WEEKDAYNUMBER, "MonthShortName", each Text.Start(Date.MonthName([Dates]),3)),
MONTHLONGNAME = Table.AddColumn(MONTHSHORTNAME, "Monthlongname", each Date.MonthName([Dates])),
MONTHNUMBER = Table.AddColumn(MONTHLONGNAME, "MonthNo", each Text.PadStart( Text.From(
Date.Month([Dates])),2,"0")),
YEAR = Table.AddColumn(MONTHNUMBER, "Year", each Date.Year([Dates])),
DATEDIFFERENCE = Table.AddColumn(YEAR, "DATE_DIFF", each Duration.Days([Dates]-

Date.From(
DateTime.LocalNow()))),
DATEFILTER = Table.AddColumn(DATEDIFFERENCE, "Filter_Date", each if [DATE_DIFF] = 0 then "Today" else if [DATE_DIFF] = 1 then "Tomorrow" else if [DATE_DIFF] = -1 then "Yesterday" else null),
WEEKFILTER = Table.AddColumn(DATEFILTER, "Filter_Week", each if
Date.IsInCurrentWeek([Dates]) then "ThisWeek" else if
Date.IsInPreviousNWeeks([Dates],1) then "Previous Week" else if Date.IsInNextNWeeks([Dates],1) then "Next Week" else null),
MONTHFILTER = Table.AddColumn(WEEKFILTER, "Filter_Month", each if
Date.IsInCurrentMonth([Dates]) then "This Month" else if
Date.IsInPreviousNMonths([Dates],1) then "Previous Month" else if Date.IsInNextNMonths([Dates],1) then "Next Month" else null),
YEARFILTER = Table.AddColumn(MONTHFILTER, "Filter_Year", each if
Date.IsInCurrentYear([Dates]) then "This Year" else if
Date.IsInPreviousNYears([Dates],1) then "Previous Year" else if Date.IsInNextNYears([Dates],1) then "Next Year" else null),
#"YYYY-MM" = Table.AddColumn(YEARFILTER, "YYYY-MM", each

Text.From([Year]) & "-" & Text.From([MonthNo]) )
in
#"YYYY-MM"

[madhumnair · Accepted solution]

Another method

let
Source = List.Dates( #"Startdate", Duration.Days( Enddate - #"Startdate" ) + 1,
#duration(1, 0, 0, 0)
),
#"Expanded Dates" = Table.FromList(
Source,
Splitter.SplitByNothing(),
{ "Dates" }, // Adds Column Name
null,
ExtraValues.Error
),
#"Duplicate Date" = Table.DuplicateColumn(#"Expanded Dates", "Dates", "Dates - Copy"),
#"DDMMYYY split" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicate Date", {{"Dates - Copy", type text}}, "en-IN"), "Dates - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"DD", "MM", "YYYY"}),
WEEKNUMBER = Table.AddColumn(#"DDMMYYY split", "WeekNo", each Text.PadStart( Text.From(
Date.WeekOfYear([Dates])),2,"0")),
WEEKNAME = Table.AddColumn(WEEKNUMBER, "Weekname", each Date.DayOfWeekName([Dates])),
WEEKDAYNUMBER = Table.AddColumn(WEEKNAME, "Weekdayno", each Date.DayOfWeek([Dates])),
MONTHSHORTNAME = Table.AddColumn(WEEKDAYNUMBER, "MonthShortName", each Text.Start(Date.MonthName([Dates]),3)),
MONTHLONGNAME = Table.AddColumn(MONTHSHORTNAME, "Monthlongname", each Date.MonthName([Dates])),
DATEDIFFERENCE = Table.AddColumn(MONTHLONGNAME, "DATE_DIFF", each Duration.Days([Dates]-

Date.From(
DateTime.LocalNow()))),
DATEFILTER = Table.AddColumn(DATEDIFFERENCE, "Filter_Date", each if [DATE_DIFF] = 0 then "Today" else if [DATE_DIFF] = 1 then "Tomorrow" else if [DATE_DIFF] = -1 then "Yesterday" else null),
WEEKFILTER = Table.AddColumn(DATEFILTER, "Filter_Week", each if
Date.IsInCurrentWeek([Dates]) then "ThisWeek" else if
Date.IsInPreviousNWeeks([Dates],1) then "Previous Week" else if Date.IsInNextNWeeks([Dates],1) then "Next Week" else null),
MONTHFILTER = Table.AddColumn(WEEKFILTER, "Filter_Month", each if
Date.IsInCurrentMonth([Dates]) then "This Month" else if
Date.IsInPreviousNMonths([Dates],1) then "Previous Month" else if Date.IsInNextNMonths([Dates],1) then "Next Month" else null),
YEARFILTER = Table.AddColumn(MONTHFILTER, "Filter_Year", each if
Date.IsInCurrentYear([Dates]) then "This Year" else if
Date.IsInPreviousNYears([Dates],1) then "Previous Year" else if Date.IsInNextNYears([Dates],1) then "Next Year" else null),
#"YYYY-MM" = Table.AddColumn(YEARFILTER, "YYYY-MM", each

[YYYY] & "-" & [MM] )
in
#"YYYY-MM"