Forum Discussion

IF's avatar
IF
Post Prodigy
5 years ago
Solved

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 =...
Need Help
IF's avatar
IF
Post Prodigy
5 years ago

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's avatar
    MFelix
    Super User
    5 years ago

    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"