Increase days on aldready existing DimDate-table

[Anonymous]

Hi,

I have this already existing table (I haven't made it) that I need to increase the years on. Right now it just goes to today's date, but I need it to increase to 2025-12-31

What should i change in the code? 

let

    Source = List.Dates,
    #"Invoke function" = Source(#date(2021, 01, 01), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())- #date(2021, 01, 01))+1, #duration(1, 0, 0, 0)),
  
    #"List to table" = Table.FromList(#"Invoke function", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    Date = Table.RenameColumns(#"List to table",{{"Column1", "Date"}}),
 
    #"Day Added" = Table.AddColumn(Date, "DayNo", each Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),
  
    #"Day Name Added" = Table.AddColumn(#"Day Added", "Day Name", each Date.ToText([Date],"ddd","en-US")),
    #"Month No Added" = Table.AddColumn(#"Day Name Added", "Month No", each Date.Month([Date])),

    #"Month Name Added" = Table.AddColumn(#"Month No Added", "Month Name", each Date.ToText([Date],"MMM","en-us")),

    #"Quarter No Added" = Table.AddColumn(#"Month Name Added", "Quarter No", each Date.QuarterOfYear([Date])),

    #"EUROPEAN Week No added" = Table.AddColumn(#"Quarter No Added", "WeekNo", each Text.PadStart(Number.ToText(Date.WeekOfYear([Date])+1),2,"0")),

    #"Year Added" = Table.AddColumn(#"EUROPEAN Week No added", "Year", each Date.Year([Date])),

    #"Year Month Added" = Table.AddColumn(#"Year Added", "Year-Month", each Number.ToText([Year])&"-"&[Month Name]),
    #"Year Week Added" = Table.AddColumn(#"Year Month Added", "Year-Week", each Number.ToText([Year])&"-"&[WeekNo]),

    #"Year Quarter Added" = Table.AddColumn(#"Year Week Added", "Year-Quarter", each Number.ToText([Year]) & "Q"& Number.ToText([Quarter No],"00")),

    #"Change type to text" = Table.TransformColumnTypes(#"Year Quarter Added",{{"Year", Int64.Type}, {"Date", type date}, {"Month No", Int64.Type},  {"Day Name", type text}, {"Month Name", type text}, {"Quarter No", Int64.Type}, {"Year-Quarter", type text}, {"Year-Month", type text}, {"WeekNo", Int64.Type}}),

    #"Sort DayName" = Table.AddColumn(#"Change type to text", "SortDayName", each Date.DayOfWeek([Date],1)),

    #"Sort YearMonth" = Table.AddColumn(#"Sort DayName", "SortYearMonth", each Number.ToText([Year])&Text.PadStart(Number.ToText([Month No]),2,"0")),
    #"Sort YearWeek" = Table.AddColumn(#"Sort YearMonth", "Sort YearWeek", each Number.ToText([Year])&Text.PadStart(Number.ToText([WeekNo]),2,"0")),

    #"Sort YearQuarter" = Table.AddColumn(#"Sort YearWeek", "SortYearQuarter", each Number.ToText([Year])&Text.PadStart(Number.ToText([Quarter No]),2,"0")),

    #"Changed Type to NO" = Table.TransformColumnTypes(#"Sort YearQuarter",{{"SortYearMonth", Int64.Type}, {"SortYearQuarter", Int64.Type}, {"SortDayName", Int64.Type}, {"Sort YearWeek", Int64.Type}, {"Year-Week", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type to NO",{{"Month Name", "Month"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "CurrentMonth", each Date.IsInCurrentMonth([Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "IsFuture", each Date.From(DateTime.LocalNow()) <= [Date]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"CurrentMonth", type logical}, {"IsFuture", type logical}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "CurrentYear", each Date.IsInCurrentYear([Date])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"CurrentYear", type logical}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type1", "Period", each (Date.Month(DateTime.LocalNow())-Date.Month([Date]) ) + 
(Date.Year(DateTime.LocalNow())-Date.Year([Date]) ) *12),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"Period", Int64.Type}})
in
    #"Changed Type2"

 
Thanks!

[ppm1]

Just add Date.EndOfYear in your second step as follows

#"Invoke function" = Source(#date(2021, 01, 01), Duration.Days(Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow()))- #date(2021, 01, 01))+1, #duration(1, 0, 0, 0)),

Pat

[Anonymous]

I got it to 2022-12-31 but not 2025?

[ppm1 · Accepted solution]

My bad for not reading your post more closely. Try this instead.

#"Invoke function" = Source(#date(2021, 01, 01), Duration.Days(#date(2025,12,31) - #date(2021, 01, 01))+1, #duration(1, 0, 0, 0)),

Pat

[Anonymous]

Thank you so much!