Hello - I have a DimDate table I created as one of my first date tables in the largest data model I have. I have a new DimDate table I want to replace it with. The original (current) DimDate table was created via the copy and paste as new table. The new one I want to replace it with was created via a copy and paste with custom periods in M through the Query Editor. Is there an easy way to replace the current DimDate table with the new one? Assuming all the columns are the same name to preserve any custom columns/calculations
Current DimDate table New Table in Desktop and Paste:
Date Table DAX
Date = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1), DATE(2025,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "WeekNum", WEEKNUM([Date]) )
New DimDate Table M Code VIA Power Query Editor
let
Calendar = #table(
{"PeriodStart", "PeriodEnd" },
{
{ #date ( 2018, 12, 30 ), #date ( 2019, 1, 26 ) },
{ #date ( 2019, 1, 27 ), #date ( 2019, 2, 23 ) },
{ #date ( 2019, 2, 24 ), #date ( 2019, 3, 23 ) },
{ #date ( 2019, 3, 24 ), #date ( 2019, 4, 20 ) },
{ #date ( 2019, 4, 21 ), #date ( 2019, 5, 18 ) },
{ #date ( 2019, 5, 19 ), #date ( 2019, 6, 15 ) },
{ #date ( 2019, 6, 16 ), #date ( 2019, 7, 13 ) },
{ #date ( 2019, 7, 14 ), #date ( 2019, 8, 10 ) },
{ #date ( 2019, 8, 11 ), #date ( 2019, 9, 7 ) },
{ #date ( 2019, 9, 8 ), #date ( 2019, 10, 5 ) },
{ #date ( 2019, 10, 6 ), #date ( 2019, 11, 2 ) },
{ #date ( 2019, 11, 3 ), #date ( 2019, 11, 30 ) },
{ #date ( 2019, 12, 1 ), #date ( 2019, 12, 28 ) },
{ #date ( 2019, 12, 29 ), #date ( 2020, 1, 25 ) },
{ #date ( 2020, 1, 26 ), #date ( 2020, 2, 22 ) },
{ #date ( 2020, 2, 23 ), #date ( 2020, 3, 21 ) },
{ #date ( 2020, 3, 22 ), #date ( 2020, 4, 18 ) },
{ #date ( 2020, 4, 19 ), #date ( 2020, 5, 16 ) },
{ #date ( 2020, 5, 17 ), #date ( 2020, 6, 13 ) },
{ #date ( 2020, 6, 14 ), #date ( 2020, 7, 11 ) },
{ #date ( 2020, 7, 12 ), #date ( 2020, 8, 8 ) },
{ #date ( 2020, 8, 9 ), #date ( 2020, 9, 5 ) },
{ #date ( 2020, 9, 6 ), #date ( 2020, 10, 3 ) },
{ #date ( 2020, 10, 4 ), #date ( 2020, 10, 31 ) },
{ #date ( 2020, 11, 1 ), #date ( 2020, 11, 28 ) },
{ #date ( 2020, 11, 29 ), #date ( 2021, 1, 2 ) }
}
),
#"Added PeriodIndex" = Table.AddIndexColumn(Calendar, "PeriodIndex", 1, 1),
#"Added DatesBetween" = Table.AddColumn ( #"Added PeriodIndex", "Date", each List.Transform ( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From ( _ ) ) ),
#"Expanded DatesBetween" = Table.ExpandListColumn ( #"Added DatesBetween", "Date" ),
#"Changed Type To Date" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"Date", type date}}),
#"Added Fiscal Period" = Table.AddColumn(#"Changed Type To Date", "Fiscal Period", each if Number.Mod ( [PeriodIndex], 13 ) = 0 then 13 else Number.Mod([PeriodIndex], 13 ), Int64.Type),
InsertYear = Table.AddColumn(#"Added Fiscal Period", "Year", each Date.Year([Date]), type number),
InsertQuarter = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]),type text),
InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, "Quarter Year Order", each [Year] * 10 + [Quarter Num], type number),
InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Month Num", each Date.Month([Date]), type number),
InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM"), type text),
InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Month Year Order", each [Year] * 100 + [Month Num], type number),
InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Week Num", each Date.WeekOfYear([Date]), type number),
InsertCalendarWk = Table.AddColumn(InsertWeek, "Week Year", each "W" & Number.ToText([Week Num]) & " " & Number.ToText([Year]), type text),
InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Week Year Order", each [Year] * 100 + [Week Num], type number),
InsertWeekEnding = Table.AddColumn(InsertCalendarWkOrder, "Week Ending", each Date.EndOfWeek([Date]), type date),
InsertDay = Table.AddColumn(InsertWeekEnding, "Month Day Num", each Date.Day([Date]), type number),
InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Year] * 10000 + [Month Num] * 100 + [Month Day Num], type number),
InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date]) + 1, type number),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd"), type text),
InsertWeekend = Table.AddColumn(InsertDayName, "Weekend", each if [Day Num Week] = 1 then "Y" else if [Day Num Week] = 7 then "Y" else "N", type text),
InsertDayNameShort = Table.AddColumn(InsertWeekend, "Day Name Short", each Date.ToText([Date], "ddd"), type text),
InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1),
InsertDayOfYear = Table.AddColumn(InsertIndex, "Day of Year", each Date.DayOfYear([Date]), type number),
InsertCurrentDay = Table.AddColumn(InsertDayOfYear, "Current Day?", each Date.IsInCurrentDay([Date]), type logical),
InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "Current Week?", each Date.IsInCurrentWeek([Date]), type logical),
InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "Current Month?", each Date.IsInCurrentMonth([Date]), type logical),
InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "Current Quarter?", each Date.IsInCurrentQuarter([Date]), type logical),
InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "Current Year?", each Date.IsInCurrentYear([Date]), type logical),
InsertCompletedDay = Table.AddColumn(InsertCurrentYear, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Date] then "Y" else "N", type text),
InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Date])) then "Y" else "N", type text),
InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Date])) then "Y" else "N", type text),
InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Date])) then "Y" else "N", type text),
InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else "N", type text),
#"Renamed Columns" = Table.RenameColumns(InsertCompletedYear,{{"PeriodStart", "FiscPeriodStart"}, {"PeriodEnd", "FiscPeriodEnd"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FiscPeriodEnd", type date}, {"FiscPeriodStart", type date}})
in
#"Changed Type"
