Forum Discussion
Weeks are numbered from 1 to 145
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
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
4 Replies
- HotChilli
Community Champion
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
That worked thanks a lot.
I have created 2 columns and checked the output
Thanks again
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
jgordon11Resolver II
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
