Forum Discussion
Week 53
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!
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.
5 Replies
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.
Thanks. On the other hand I have just realized that 2015 has 53 week but not 2016. 🤔
All the best!
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.
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])
thenText.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!
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"
