cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Increase days on aldready existing DimDate-table

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!

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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

 

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

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

Thank you so much!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (6,448)