地表最强Power Query 日期表

let
// To be turned into parameter
startDate = #date(2017,01,01) as date, 
// To be turned into parameter
endDate = #date(2022,12,31) as date, 
// To be turned into parameter
culture = "en-US" as text,

// Beginning of actual query 👇 
Source = List.Dates(startDate, Duration.Days(Duration.From(endDate - startDate)) + 1, #duration(1, 0, 0, 0)),

TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns , "Year", each Date.Year([Date]),type text),
InsertQuarterNumber = Table.AddColumn(InsertYear, "Quarter Number", each Date.QuarterOfYear([Date])),
InsertQuarterName = Table.AddColumn(InsertQuarterNumber, "Quarter Name", each "Q" & Number.ToText([Quarter Number])),
InsertMonth = Table.AddColumn(InsertQuarterName, "Month Number", each Date.Month([Date]), type text),
InsertStartOfMonth = Table.AddColumn(InsertMonth, "Start Of Month", each Date.StartOfMonth([Date]), type date),
InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "End Of Month", each Date.EndOfMonth([Date]), type date),
InsertDayOfMonth = Table.AddColumn(InsertEndOfMonth, "Day Of Month", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDayOfMonth, "DateInt", each [Year]*10000 + [Month Number]*100 + [Day Of Month]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM", culture), type text),
InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM", culture), type text),
InsertShortMonthNameYear = Table.AddColumn(InsertShortMonthName, "Month Short and Year", each [Month Name Short] & " " & Number.ToText([Year]), type text),
InsertCalendarMonth = Table.AddColumn(InsertShortMonthNameYear, "Month and Year", each [Month Name]& " " & Number.ToText([Year]),type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter and Year", each "Q" & Number.ToText([Quarter Number]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Day of Week", each Date.DayOfWeek([Date]) + 1),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd", culture), type text),
InsertShortDayName = Table.AddColumn(InsertDayName, "Day Name Short", each Date.ToText([Date], "ddd", culture), type text),
InsertEndOfWeek = Table.AddColumn(InsertShortDayName , "End Of Week", each Date.EndOfWeek([Date]), type date),
InsertedStartOfWeek = Table.AddColumn(InsertEndOfWeek, "Start of Week", each Date.StartOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertedStartOfWeek, "Week of Year Number", each Date.WeekOfYear([Date])),
InsertMonthWeekNumber= Table.AddColumn(InsertWeekNumber, "Week of Month Number", each Date.WeekOfMonth([Date])),
InsertYearMonthNumber = Table.AddColumn(InsertMonthWeekNumber,"Year and Month", each [Year] * 100 + [Month Number]),
InsertYearAndQuarterNumber = Table.AddColumn(InsertYearMonthNumber, "Year and Quarter", each [Year] * 100 + [Quarter Number]),
InsertYearAndWeekNumber = Table.AddColumn(InsertYearAndQuarterNumber, "Year and Week", each [Year] * 100 + [Week of Year Number]),
InsertDecadeName = Table.AddColumn(InsertYearAndWeekNumber, "Decade Name", each Text.Range(Text.From([Year]), 0, 3) & "0s"),
InsertDecadeNumber = Table.AddColumn(InsertDecadeName, "Decade Number", each Text.Range(Text.From([Year]), 0, 3) & "0"),
InsertStartOfQuarter = Table.AddColumn(InsertDecadeNumber, "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
InsertEndOfQuarter = Table.AddColumn(InsertStartOfQuarter, "End of Quarter", each Date.EndOfQuarter([Date]), type date),
InsertDayOfYear = Table.AddColumn(InsertEndOfQuarter, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
InsertIsWeekday = Table.AddColumn(InsertDayOfYear, "Is Weekday", each if [Day of Week] = 1 or [Day of Week] = 7 then 0 else 1),
InsertIsWeekend = Table.AddColumn(InsertIsWeekday, "Is Weekend", each if [Day of Week] = 1 or [Day of Week] = 7 then 1 else 0),
#"Reordered Columns" = Table.ReorderColumns(InsertIsWeekend,{"DateInt", "Date", "Year", "Year and Quarter", "Year and Month", "Year and Week", "Quarter Name", "Quarter Number", "Quarter and Year", "Start of Quarter", "End of Quarter", "Month Name", "Month Name Short", "Month Number", "Month and Year", "Month Short and Year", "Start Of Month", "End Of Month", "Week of Year Number", "Week of Month Number", "Start of Week", "End Of Week", "Day of Year", "Day Of Month", "Day of Week", "Day Name", "Day Name Short", "Decade Name", "Decade Number", "Is Weekday", "Is Weekend"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"DateInt", Int64.Type}, {"Year", Int64.Type}, {"Year and Quarter", Int64.Type}, {"Year and Month", Int64.Type}, {"Year and Week", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Number", Int64.Type}, {"Week of Year Number", Int64.Type}, {"Week of Month Number", Int64.Type}, {"Day of Year", Int64.Type}, {"Day Of Month", Int64.Type}, {"Day of Week", Int64.Type}, {"Decade Number", Int64.Type}, {"Is Weekday", Int64.Type}, {"Is Weekend", Int64.Type}, {"Quarter Name", type text}, {"Decade Name", type text}})
in
#"Changed Type"