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"

       

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Try this 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}}),
    #"Added Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Week of Year" = Table.AddColumn(#"Added Year", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Added WeekYear" = Table.AddColumn(#"Added Week of Year", "WeekYear", each Text.End("0" & Text.From([Week of Year]), 2) & "." & Text.From([Year]), type text),
    #"Added WeekYear Sort" = Table.AddColumn(#"Added WeekYear", "WeekYear Sort", each [Year] * 100 + [Week of Year], Int64.Type),
    #"Added Start of Week" = Table.AddColumn(#"Added WeekYear Sort", "Start of Week", each Date.StartOfWeek([Date]))
in
    #"Added Start of Week"

         

         

        It returns this:

         

        You cannot remove duplicates. Your solution above is missing a ton of dates and cannot be used as a date table. The above code and table can. I included a WeekYear sort code to allow you to sort by columns on the WeekYear, which will sort alphabetically, not numerically, so 01.2019 would sort before 52.2018 for example unless you use my SortBy column.

         

        Also, if you publish more code, try to use the </> button in the toolbar to put it in a code box so it the forum doesn't mangle it. ğŸ˜€