using the Calendar function and power query

Hi Michael,

I recommend you to create the table in Power Query.

Try the following script by creating a blank query. Pleas adjust the first two lines (StartDate and EndDate) to your needs directly in the query editor.

let
    StartDate = #date(2020,1,1),
    EndDate = #date(2023,12,31),
    DateList = List.Dates(StartDate, Number.From(EndDate) - Number.From(StartDate), #duration(1, 0, 0, 0)),
    DatesAsTable = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenamedColumnDate = Table.RenameColumns(DatesAsTable, {{"Column1", "PK_Date"}}),
    ChangedTypeDate = Table.TransformColumnTypes(RenamedColumnDate,{{"PK_Date", type date}}),
    Year = Table.AddColumn(ChangedTypeDate, "Year", each Date.Year([PK_Date])),
    QuarterOfYear = Table.AddColumn(Year, "QuarterofYear", each Date.QuarterOfYear([PK_Date])),
    QuarterNameOfYear = Table.AddColumn(QuarterOfYear, "QuarterNameOfYear", each "Q" & Number.ToText([QuarterofYear])),
    QuarterWithYear = Table.AddColumn(QuarterNameOfYear, "QuarterWithYear", each Number.ToText([Year]) & "-" & [QuarterNameOfYear]),
    MonthNum = Table.AddColumn(QuarterWithYear, "MonthNum", each Date.Month([PK_Date])),
    MonthName = Table.AddColumn(MonthNum, "MonthName", each Date.ToText([PK_Date], "MMMM")),
    MonthNameShort = Table.AddColumn(MonthName, "MonthNameShort", each Date.ToText([PK_Date], "MMM")),
    MonthWIthYear = Table.AddColumn(MonthNameShort, "MonthWithYear", each Number.ToText([Year]) & "-" & [MonthNameShort]),
    MonthNameSorting = Table.AddColumn(MonthWIthYear, "MonthNameSorting", each [Year] * 10 + [MonthNum]),
    WeekNumOfYear = Table.AddColumn(MonthNameSorting, "WeekNumOfYear", each Date.WeekOfYear([PK_Date])),
    WeekNameOfYear = Table.AddColumn(WeekNumOfYear, "WeekNameOfYear", each "KW" & Text.PadStart(Number.ToText([WeekNumOfYear]),2,"0")),
    WeekWithYear = Table.AddColumn(WeekNameOfYear, "WeekWithYear", each Number.ToText([Year]) & "-" & [WeekNameOfYear]),
    DayNumOfYear = Table.AddColumn(WeekWithYear, "DayNumOfWeek", each Date.DayOfWeek([PK_Date])+1),
    DayNameOfWeek = Table.AddColumn(DayNumOfYear, "DayNameofWeek", each Text.Start(Date.DayOfWeekName([PK_Date]), 2)),
    ChangeType = Table.TransformColumnTypes(DayNameOfWeek,{{"Year", Int64.Type}, {"QuarterofYear", Int64.Type}, {"MonthNum", Int64.Type}, {"WeekNumOfYear", Int64.Type}, {"QuarterNameOfYear", type text}, {"QuarterWithYear", type text}, {"MonthName", type text}, {"MonthNameShort", type text}, {"MonthWithYear", type text}, {"WeekNameOfYear", type text}, {"DayNameofWeek", type text}, {"DayNumOfWeek", Int64.Type}, {"MonthNameSorting", Int64.Type}})
in
    ChangeType

creat blank query 

adjust parameters

result

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.