Forum Discussion

IF's avatar
IF
Post Prodigy
5 years ago
Solved

dynamic week table

Hi,

I want to develop a dynamic week table, which should list the weeks for last 119 weeks (or last three years). The table will always keep the last 119 weeks. I plan to use the following, but the the performance is not really good with this one. is there any other way to develop it?

 

let
StartDate= Date.AddWeeks(DateTime.LocalNow(), -119),
EndDate = Date.AddWeeks(DateTime.LocalNow(), -1),
DateList = List.Dates(DateTime.Date(StartDate), Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
#"Sorted Items" = List.Sort(DateList,Order.Ascending),
#"Converted to Table" = Table.FromList(#"Sorted Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
#"Renamed Columns"

 

All the best,

 

Need Help
  • ziying35's avatar
    ziying35
    5 years ago

    IF 

    See if the following is what you're trying to achieve?

    //output
let
    Source = List.Generate(
                ()=> {Date.StartOfWeek(Date.AddWeeks(DateTime.LocalNow(), -119)), 1}, 
                each _{0} < Date.StartOfWeek(Date.AddWeeks(DateTime.LocalNow(), -1)), 
                each {Date.AddDays(_{0}, 7), _{1}+1}, 
                each {Text.Format("#{1}.#{0}", {Date.Year(_{0}), Date.WeekOfYear(_{0})}), _{1}}
            ),
    Tbl = Table.FromRows(Source, type table[#"Week No.Year" = text, Index = number])
in
    Tbl

11 Replies

  • ziying35's avatar
    ziying35
    Impactful Individual
    5 years ago

    Hi, IF 

    Try this:

    // date
let
    Source = List.Generate(
                ()=> Date.AddWeeks(DateTime.LocalNow(), -119), 
                each _< Date.AddWeeks(DateTime.LocalNow(), -1), 
                each Date.AddDays(_, 1), 
                Date.From
            ),
    Tbl = Table.FromColumns({Source}, type table[Date = date])
in
    Tbl
    • IF's avatar
      IF
      Post Prodigy
      5 years ago

      Hi,

      Is it possible to show the number of the weeks directly instead of showing each day?

      Best

      • ziying35's avatar
        ziying35
        Impactful Individual
        5 years ago

        IF 

        What does that mean, I'm not sure I understand, can you simulate some data examples? That way I can understand more quickly what kind of desired outcome you're trying to achieve.

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Try this method IF 

    let
    Source = {Number.From(Date.AddWeeks(DateTime.Date(DateTime.LocalNow()), -119))..Number.From(Date.AddWeeks(DateTime.Date(DateTime.LocalNow()), -1))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}})
in
    #"Changed Type"

    I use this to generate date tables going back decades and it works well. I just did one yesterday that went to Jan 1, 1999 and the 8K rows of the date table with about 30 columns ultimately loaded in a few seconds, so 118 weeks is super fast.

    • IF's avatar
      IF
      Post Prodigy
      5 years ago

      How can I show the week values such as:

      06.2019

      07.2019

      08.2019

      09.2019

      10.2019

      11.2019

      12.2019

      13.2019