Weeknum issue

Question

Hi all, I would like to consult a formula in Power query, I have extracted my source data with weeknum, as some of Jan 1 is connected with the 31/12 last year, my system will make them as week 53, how could I use the formula to detect if I have week 53, then it will change to week 1 and actual week 1 will change to week 2 and so on, of cause, if that is no week 53, it will follow the original weeknum, thanks

Answer 1

@Alex Wong

Save this as a separate query, name it fnShiftWeekNumber for now:

(tbl as table, optional weekColumn as nullable text) as table =>  
let  
    Source = tbl,  
    WeekColum = if weekColumn is null  
        then "Weeknum" else weekColumn,  
  
    MaxWeeks = Number.FromText(  
        Record.Field(  
            Table.Max(Source, {WeekColum}),  
            WeekColum  
        )  
    ),  
    ShiftedWeeks = Table.TransformColumns(Source,  
        {WeekColum, each  
            Text.PadStart(  
                Number.ToText(  
                    if Number.FromText(_) < 53  
                    then Number.FromText(_) +1  
                    else 1  
                ),  
                2, "0"  
            ),  
            type text  
        }  
    )  
in  
    if MaxWeeks = 53 then ShiftedWeeks else Source  

NB: If you don't pass a column name to the function it'll use "Weeknum" as default

A query for you to double-check - change 52 <> 53 in the 1st List.Transform:

let  
    Source = Table.FromColumns(         // Demo table  
        {  
            List.Transform({1..53}, each Text.PadStart(Text.From(_), 2, "0")),  
            List.Transform({1..52}, each "Comment " & Text.From(_))  
        },  
        type table [Weeknum=text, OtherColumn=text]  
    ),  
    ShiftedWeekNum = fnShiftWeekNumber(Source) // Or...  
//  ShiftedWeekNum = fnShiftWeekNumber(Source, "Weeknum") with this Source  
in  
    ShiftedWeekNum