How to create calendar tables in Power Query

1. In the “Advanced Editor” window, paste the following code. Choose the start date and end date for your calendar table.

let

    // Start and end dates for the calendar table

    StartDate = #date(2021,1,1),

    EndDate = #date(2022,12,31),


    // Number of days in the calendar table

    NumberOfDays = Duration.Days(EndDate-StartDate)+1,


    // Generate a list of dates

    DatesList = List.Dates(StartDate, NumberOfDays, #duration(1,0,0,0)),


    // Convert the list of dates to a table

    CalendarTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error)

in

    CalendarTable


2. Click on “Done” to close the “Advanced Editor” window.


***********************

Create Monthly level calendar table 

let

    first_date = #date(2024, 1, 1),

    last_day = #date(2026, 1, 1),

    num_months = ((Date.Year(last_day) - Date.Year(first_date)) * 12 + Date.Month(last_day) - Date.Month(first_date)),

    list_of_num = List.Numbers(0, num_months, 1),

    table_from_list = Table.FromValue(list_of_num, [DefaultColumnName = "Index"]),

    add_col_year = Table.AddColumn(table_from_list, "Year", each Date.Year(Date.AddMonths(first_date, [Index])), Int64.Type),

    add_col_month = Table.AddColumn(add_col_year, "Mes", each Date.Month(Date.AddMonths(first_date, [Index])), Int64.Type)

in

    add_col_month


Month Level Table


let

    first_date = #date(2024, 1, 1),

    last_day = #date(2026, 1, 1),

    num_months = ((Date.Year(last_day) - Date.Year(first_date)) * 12 + Date.Month(last_day) - Date.Month(first_date)),

    list_of_num = List.Numbers(0, num_months, 1),

    table_from_list = Table.FromValue(list_of_num, [DefaultColumnName = "Index"]),

    add_col_year = Table.AddColumn(table_from_list, "Year", each Date.Year(Date.AddMonths(first_date, [Index])), Int64.Type),

    add_col_month = Table.AddColumn(add_col_year, "Month_id", each Date.Month(Date.AddMonths(first_date, [Index])), Int64.Type),

    add_calendar_month = Table.AddColumn(add_col_month, "Calendar_Month", each Date.ToText( Date.AddMonths(first_date, [Index]), "yyyy-MM" )),

    add_month_name = Table.AddColumn(add_calendar_month, "Month_Name", each Date.MonthName(Date.AddMonths(first_date, [Index])), Int64.Type)

in

    add_month_name

Also good Reference: https://gorilla.bi/power-query/date-table-with-monthly-increments/

DimDate


let

  DateStart = #date(2024, 1, 1),

  DateEnd = #date(2025, 12, 31),

  WeekStart = Day.Monday,


  Dates = List.Dates(DateStart, Duration.Days( DateEnd - DateStart )+1, #duration(1,0,0,0)),

  Source = Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(Dates, Splitter.SplitByNothing()),{{"Column1", "Date"}}),{{"Date", type date}}),

  #"Add Year" = Table.AddColumn(Source, "Year", each Date.Year([Date]), Int32.Type),

  #"Add MonthNum" = Table.AddColumn(#"Add Year", "MonthNum", each Date.Month([Date]), Int64.Type),

  #"Add Month" = Table.AddColumn(#"Add MonthNum", "Month", each Date.ToText([Date], [Format="MMMM"]), type text),

  #"Add Month short" = Table.AddColumn(#"Add Month", "Month short", each Date.ToText([Date], [Format="MMM"]), type text),

  #"Add WeekNum" = Table.AddColumn(#"Add Month short", "WeekNum", each Date.WeekOfYear([Date], WeekStart), Int64.Type),

  #"Add Week" = Table.AddColumn(#"Add WeekNum", "Week", each Date.StartOfWeek([Date], WeekStart), type date),

  #"Marked key columns" = Table.AddKey(#"Add Week", {"Date"}, false),

  #"Month Year" = Table.AddColumn(#"Marked key columns", "Year Month", each Date.ToText([Date], "yyyy-MM"),type text)

in

  #"Month Year"

Ref: https://stackoverflow.com/questions/77876585/how-to-create-calendar-table-with-year-and-week-number-in-power-query-m