Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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