Weeks are numbered from 1 to 145

[SarahK]

Need help please

i am working on a dataset and I don't have any dates columns except a week column that starts from 1 to 145.

I am interested in converting this column to week numbers from 1 to 52

which means that week 53 will correspond to 1 and week 54 will correspond to 2 and so on. How can I do that

thanks in advance 

[jgordon11]

An example:

let
     Source = #table(type table [WeekNum = Int64.Type], List.Zip({{1 .. 145}})),
     tbl = Table.TransformColumns(Source, {"WeekNum", each Number.Mod(_ - 1, 52) + 1})
in
tbl

[HotChilli · Accepted solution]

You can add a column which uses Number.Mod (https://learn.microsoft.com/en-us/powerquery-m/number-mod ) to divide the week column by 52.  That will work great for all values except the multiples of 52 which will show as zero.

So you can add another column which says 'if my newvalue is 0 then 52 else newvalue'

--

If you're confident with the code you could combine both steps in the first column 

[SarahK]

That worked thanks a lot.

I have created 2 columns and checked the output

Thanks again

[v-cgao-msft]

Hi @SarahK ,

If some answers are helpful to you, don't forget to mark it as a solution. This will help more people to find answers to similar questions more quickly, thanks in advance!

Best Regards,
Gao
Community Support Team