Week 53

[IF]

Hi,

I get data from SAP BW and use the weekly table to get the data.

This code generates 53.2019 and 53.2018, which is not correct. How could I improve it?

The code is:

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 Custom" = Table.AddColumn(#"Changed Type", "YearWeek", each Text.PadStart(Number.ToText(Date.WeekOfYear([Date])),2,"0")

& "." &

Number.ToText(Date.Year([Date]))),

    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"YearWeek"}),

    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Date", Order.Descending}}),

    #"Inserted Week of Year" = Table.AddColumn(#"Sorted Rows", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),

    #"Inserted Year" = Table.AddColumn(#"Filtered Rows", "Year", each Date.Year([Date]), Int64.Type),

    #"Added Custom1" = Table.AddColumn(#"Inserted Year", "WeekYear Sort", each [Year]*100+[Week of Year])

in

    #"Added Custom1"

The error code is:

Thanks in advance!

[IF]

Hi,

Thanks for the  answer. I am not sure if I did it in the way that you explained, but this doesn't work:

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 Custom" = if Date.IsLeapYear([Date])
then

Text.PadStart(Number.ToText(Date.WeekOfYear([Date])),2,"0")

& "." &

Number.ToText(Date.Year([Date])) else if Date.WeekOfYear([Date]) = 53 then Text.PadStart("1",2,"0")

& "." &

Number.ToText(Date.Year([Date]) + 1 ) else Text.PadStart(Number.ToText(Date.WeekOfYear([Date])),2,"0")

& "." &

Number.ToText(Date.Year([Date])),

#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"YearWeek"}),

#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Date", Order.Descending}}),

#"Inserted Week of Year" = Table.AddColumn(#"Sorted Rows", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),

#"Inserted Year" = Table.AddColumn(#"Filtered Rows", "Year", each Date.Year([Date]), Int64.Type),

#"Added Custom1" = Table.AddColumn(#"Inserted Year", "WeekYear Sort", each [Year]*100+[Week of Year])

in

#"Added Custom1"

All the best!

[MFelix]

Hi @IF ,

I have explained myself incorretly you need to change only the custom formula. I have check the full code and in order to have things matching you need to also change additional column of the week and year check full code below:

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 Custom" = Table.AddColumn(#"Changed Type", "YearWeek", each if Date.IsLeapYear([Date])
then

Text.PadStart(Number.ToText(Date.WeekOfYear([Date])),2,"0")

& "." &

Number.ToText(Date.Year([Date])) else if Date.WeekOfYear([Date]) = 53 then Text.PadStart("1",2,"0")

& "." &

Number.ToText(Date.Year([Date]) + 1 ) else Text.PadStart(Number.ToText(Date.WeekOfYear([Date])),2,"0")

& "." &

Number.ToText(Date.Year([Date]))),

    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"YearWeek"}),

    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Date", Order.Descending}}),

    #"Inserted Week of Year" = Table.AddColumn(#"Sorted Rows", "Week of Year", each if Date.IsLeapYear([Date])  then Date.WeekOfYear([Date])
 else if Date.WeekOfYear([Date]) = 53 then 1
else

Date.WeekOfYear([Date])),
    #"Inserted Year" = Table.AddColumn(#"Inserted Week of Year", "Year", each if Date.IsLeapYear([Date])  then Date.WeekOfYear([Date])
 else if Date.WeekOfYear([Date]) = 53 then Date.Year([Date]) + 1
else

Date.Year([Date])),

    #"Added Custom1" = Table.AddColumn(#"Inserted Year", "WeekYear Sort", each [Year]*100+[Week of Year])
in
    #"Added Custom1"

[MFelix]

Hi @IF 

Not sure if this is the correct option about your data but believe that you only consider 53 weeks on leap years, if this is the fact change your week.year column to the following code:

if Date.IsLeapYear([Date])  
then

Text.PadStart(Number.ToText(Date.WeekOfYear([Date])),2,"0")

& "." &

Number.ToText(Date.Year([Date])) else  if  Date.WeekOfYear([Date]) = 53  then  Text.PadStart("1",2,"0")

& "." &

Number.ToText(Date.Year([Date]) + 1 ) else Text.PadStart(Number.ToText(Date.WeekOfYear([Date])),2,"0")

& "." &

Number.ToText(Date.Year([Date]))

In this case I'm picking up the week were the number is 53 and making it on the first week of the other year.

[IF]

Thanks. On the other hand I have just realized that 2015 has 53 week but not 2016. 🤔 

All the best!

[MFelix · Accepted solution]

Hi @IF ,

That has to do with the start of the week check the post below with some explanations about it.

https://www.timeanddate.com/date/week-numbers.htmlhttps://www.quora.com/What-year-will-have-53-weeks

Don't forget to mark the correct answer to help others.