I calculated ISO week number in Power Query following instructions from Wikipedia.
Here is how it goes. M code is at the end of this post.
It all starts with weekday calculation. By default Power Query Date.DayOfWeek function returns a number between 0 and 6 starting from Sunday. To return weekday number according to ISO8601 rules, you have to add optional first-weekday value and change numbering from 1 to 7. For this demonstration, I also generated a list of dates.
=Date.DayOfWeek([Date], Day.Monday)+1
After this little adjustment, it is possible to calculate ISO week number in Power Query with all necessary conditions.
Using ISO weekday numbers (running from 1 for Monday to 7 for Sunday), subtract the weekday from the ordinal date, then add 10. Divide the result by 7. Ignore the remainder; the quotient equals the week number. If the week number thus obtained equals 0, it means that the given date belongs to the preceding (week-based) year. If a week number of 53 is obtained, one must check that the date is not actually in week 1 of the following year.
=if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0 then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7) else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4)) then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)
Looking for ISO year calculation? If you have ISO week numbering then here is the easiest way how to calculate ISO year in Power Query.
It might be a useful addition to a flexible calendar table that you can create with Power Query.
Here is the M code that you can put in Blank Query and try by yourself.
//datacornering.com
let
Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 365, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Weekday Number" = Table.AddColumn(#"Changed Type", "Weekday Number", each Date.DayOfWeek([Date], Day.Monday)+1),
#"ISO Week Number" = Table.AddColumn(#"Weekday Number", "ISO Week Number", each if
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
then
Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
else if
(Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
then
1
else
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7))
in
#"ISO Week Number"
This is just what I need! Can you also show how to add the proper year? based on the IOS column?
What do you mean with “proper year”? Please give an example.
He means this:
Imagine a column like “Year – Weeknumber” > “2020 – 53”.
In 2021, the 1st of July fell on a Friday. This means that the 1st, 2nd and 3rd of January belong to week 53 of the previous year. These dates, from 2021, should thus fall under “2020 – 53”.
Consequently, the week of 04-01-2021 to 10-01-2021 is “2021 – 01”
I looked it up, and every in my region (Europe) the first week of the year is the week with at least 4 days in the new year, as Robert explained (think the 1st of july was a type-o and should be 1st of january). Also our weeks start on a Monday, in some regions it sunday.
If you calculated the ISO week and had put it in a column [Week]
and calculated the Year and put it in a Column [Year]
and have the column with the Date [Date]
you can Calculate the ISO Year as follows:
DAX:
Iso Year = IF([Week]>51 && DAY([Date])<4,[Year]-1,IF([Week]28,[Year]+1, [Year]))
M:
if ([Week]>51 and Date.Day([Date])<4) then [Year]-1 else if ([Week]28) then [Year]+1 else [Year]
DAY([Date]) and Date.Day([Date]) tells you the day number of the month (1-31)
Hope this helps.
Sorry, I see i pasted my M code wrongly
if ([Week]>51 and Date.Day([Date])<4) then [Year]-1 else if ([Week]28) then [Year]+1 else [Year]
Thank you for your answer!
Thank you for this Bruce, really helpful. Can you explain what the ‘else if ([Week]28)’ refers to? I can’t get it to work properly.
Hi Bruce,
Is there supposed to be a symbol before the 28?
Your help is greatly appreciated
I am sooooo incredibly sorry for leaving you guys blind for almost a year! I noted my mistake this morning and here’s the correction:
I hope this makes a lot more sense:
DAX:
Iso Year = IF([Week]>51 && DAY([Date])<4,[Year]-1,IF([Week]28,[Year]+1, [Year]))
M:
if ([Week]28) then [Year]+1
Thought process:
If the week number is smaller than 2 and the day of the month is greater than 28, this would imply that it would be week 1 and a date in december, so the year of the week should be +1
For some reason this forum keeps cutting off my code! I sure hope this gets posted correctly no!
This:
([Week]28)
Should be replaced by this!
([Week]28)
Thought process:
If the week number is smaller than 2 and the day of the month is greater than 28, this would imply that it would be week 1 and a date in december, so the year of the week should be +1
Hello everyone, could someone provide me with the finished excel file? i download a csv file every week. this file has date, open, high, low and close columns. on the right i want to display the year, then the quarter, then the month, then the iso week! and on the far right the date. can someone provide me with such a file? i don’t know anything about programming and i think it wouldn’t be that much work. I can also provide you with the csv file if that helps
Brilliant! Thank you so much Janis.
hi,
I think you forgot a sign on: if ([Week]28)…
Great!
if ([Week]>51 and Date.Day([Date])51 and Date.Day([Date])<4) then [Year]-1 else [Year]
What are the limitations of not using the week28 comparison.
Hi Khoi,
Is there a sign that should be before the 51?
For 2021 I have the first 3 days showing up as week 53. How do I modify the code to have these first 3 days show as week 1
That’s the normal result of ISO week numbers – every week should have 7 days.
https://en.wikipedia.org/wiki/ISO_week_date
It is not ideal and the one and the only way to number weeks.
Try to use Date.WeekOfYear function https://docs.microsoft.com/en-us/powerquery-m/date-weekofyear
change monday -> sunday
Hello
I do have same error of 53, weeks showing of first day of Feb as 53.
I used above query, but its showing week 5 and 6.
Please help.
Try to use fuction Date.WeekOfYear function https://docs.microsoft.com/en-us/powerquery-m/date-weekofyear
Hello everyone, could someone provide me with the finished excel file? i download a csv file every week. this file has date, open, high, low and close columns. on the right i want to display the year, then the quarter, then the month then the iso week! I can provide the CVS file if it helps.
Hi,
How do you implement this adjustment if you have a start and an end date for each row?
I want to split an amount across weeks within 2021 determined by a range of days from start to end of period.
Thanks!
Howdy! Careful with the year… Check out the below.
let
UTCDate = DateTimeZone.UtcNow()
in
(if Date.DayOfWeek(UTCDate,Day.Thursday) < 4
then Number.ToText(Date.Year(Date.AddDays(UTCDate,-Date.DayOfWeek(UTCDate,Day.Thursday))))
else Number.ToText(Date.Year(UTCDate)))
&
"w"
&
Text.PadStart(
Number.ToText(
if Number.RoundDown((Date.DayOfYear(UTCDate)-(Date.DayOfWeek(UTCDate, Day.Monday)+1)+10)/7)=0
then Number.RoundDown((Date.DayOfYear(#date(Date.Year(UTCDate)-1,12,31))-(Date.DayOfWeek(#date(Date.Year(UTCDate)-1,12,31), Day.Monday)+1)+10)/7)
else if (Number.RoundDown((Date.DayOfYear(UTCDate)-(Date.DayOfWeek(UTCDate, Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year(UTCDate),12,31), Day.Monday)+1<4))
then 1
else Number.RoundDown((Date.DayOfYear(UTCDate)-(Date.DayOfWeek(UTCDate, Day.Monday)+1)+10)/7)
),
2,
"0"
)
Still doesn’t work i added some spaces:
( [Week] 28)