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 ...
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
IF's avatar
IF
Post 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"

 

ziying35's avatar
ziying35
Impactful Individual
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