Forum Discussion

IF's avatar
IF
Icon for Post Prodigy rankPost 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 ...
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
ziying35's avatar
ziying35
Icon for Impactful Individual rankImpactful 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
    Icon for Post Prodigy rankPost 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
      Icon for Impactful Individual rankImpactful 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.

      • IF's avatar
        IF
        Icon for Post Prodigy rankPost Prodigy
        5 years ago

        Is there any way to display it like:

        Week No.Year

        25.2018
        26.2018
        27.2018
        28.2018
        29.2018


        I think it is possible to do it with following, but I don't know if this is a right way.

        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}}),
        #"Inserted Week of Year" = Table.AddColumn(#"Changed Type", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
        #"Inserted Year" = Table.AddColumn(#"Inserted Week of Year", "Year", each Date.Year([Date]), Int64.Type),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Week of Year", type text}, {"Year", type text}}, "en-US"),{"Week of Year", "Year"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),
        #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
        #"Inserted Start of Week" = Table.AddColumn(#"Removed Duplicates", "Start of Week", each Date.StartOfWeek([Date]), type date),
        #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date]), type date),
        #"Added Index" = Table.AddIndexColumn(#"Inserted End of Week", "Index", 1, 1, Int64.Type)
        in
        #"Added Index"