question

AlexWong-5960 avatar image
0 Votes"
AlexWong-5960 asked Lz-3068 answered

Weeknum issue

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

96742-image.png


power-query-not-supported
image.png (47.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi again AlexWong-5960. Couple of questions:

  1. How many Years of data do you have in your dataset?

  2. Do you want to keep the Weeknum as Text values at the end or do you expect numbers instead?


0 Votes 0 ·

Dear @Lz-3068, I have only 1 year (2021) data in this query, but as this query will be repeat to use for next year and will be use to extract data from 2020 and 2019 (same format of data source as 2021) I might have a year which does not have week 53....and that is fine if I keep it in text values, thank you.

0 Votes 0 ·

1 Answer

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@AlexWong-5960

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.