M functions to create ISO 8601 Week & Year from dates

power-query-iso-week-year.md

DEPRECATED: see this updated fork

from https://social.technet.microsoft.com/Forums/en-US/2a85c825-c75b-4622-98a1-25547dec49c8/enhancement-request-iso-week-number?forum=powerquery

(DAX's weeknum(x, 21) doesn't give the ISO Year)

InsertISOYear = Table.AddColumn(
    previousstepname,
    "ISOYear",
    each Date.Year(
        Date.AddDays(
            [Date],
            3 - Date.DayOfWeek([Date], 1)
        )
    ),
    type number
),

InsertRefDate = Table.AddColumn(
    InsertISOYear,
    "RefDate",
    each #date([ISOYear], 1, 3)
),

InsertISOWeek = Table.AddColumn(
    InsertRefDate,
    "ISOWeek",
    each Number.IntegerDivide(
        Duration.Days( [Date] - [RefDate] ) + Date.DayOfWeek([RefDate], 0) + 6,
        7
    ),
    type number
),

InsertWeekInt = Table.AddColumn(
    InsertISOWeek,
    "WeekInt",
    each [ISOYear] * 100 + [ISOWeek],
    type number
),

InsertCalendarWeek = Table.AddColumn(
    InsertWeekInt,
    "WeekInCalendar",
    each Number.ToText([ISOYear]) & Number.ToText([ISOWeek], "-W00")
),

RemovedColumns = Table.RemoveColumns(
    InsertCalendarWeek,
    {"RefDate"}
)