Embedded Files
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
Page updated
Google Sites
Report abuse