dynamic week table

[IF]

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,

[edhans]

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]

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

[ziying35]

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]

Hi,

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

Best

[edhans]

@IF on the weeks, you have to start with the date. Date tables must be all dates between the first and last date. I have a fully dynamic date table linked below with the weeks in it as a new column.

Creating a Dynamic Date Table in Power Query

https://bit.ly/DateTableByEd

[ziying35]

@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]

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 · Accepted solution]

@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

[edhans]

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. 😀

[IF]

I tried this, but I have a source. I must show values: 08.2019 since I get data from another source and make ralationship between the week field. If I use 8.2019 it gives error. Is it possible to show week.year values: 08.2019, 09.2019, 10.2019, etc.? Regards

[edhans]

See my M code above @IF - it will now show the leading zero if needed. Refresh your page to get it.