Номер недели ISO в Power Query / M и Power BI

http://excel-inside.pro/ru/blog/2018/03/06/%D0%BD%D0%BE%D0%BC%D0%B5%D1%80-%D0%BD%D0%B5%D0%B4%D0%B5%D0%BB%D0%B8-iso-%D0%B2-power-query-m-%D0%B8-power-bi

https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca

Иногда, особенно во время работы с таблицами-календарями, нам необходимо определить номер недели по ISO. К сожалению, «родной» функции для этого в Power Query или в Power BI нет, и для получения нужного результата приходится писать свою.

Спасибо Catherine Monier , Microsoft Excel MVP, за ссылку на готовую функцию для Power Query “Date to ISO Week” . Также по этой ссылке можно найти и обратную функцию, переводящую даты формата 2017-W02-7 в обычную дату:

based on https://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date

M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's weeknum(x, 21) doesn't give the correct ISO Week-Year.

Date → ISO Week

/*

based on <https://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date >

M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's

`weeknum(x, 21)` doesn't give the correct ISO Week-Year.

homepage: <https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca >

*/

 

let

    getISO8601Week = (someDate as date) =>

        let

            getDayOfWeek = (d as date) =>

                let

                    result = 1 + Date.DayOfWeek(d, Day.Monday)

                in

                    result,

 

            getNaiveWeek = (inDate as date) =>

                let

                    // monday = 1, sunday = 7

                    weekday = getDayOfWeek(inDate),

 

                    weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),

 

                    ordinal = Date.DayOfYear(inDate),

 

                    naiveWeek = Number.RoundDown(

                        (ordinal - weekday + 10) / 7

                    )

                in

                    naiveWeek,

 

            thisYear = Date.Year(someDate),

 

            priorYear = thisYear - 1,

 

            nwn = getNaiveWeek(someDate),

 

            lastWeekOfPriorYear =

                getNaiveWeek(#date(priorYear, 12, 28)),

 

// http://stackoverflow.com/a/34092382/2014893

            lastWeekOfThisYear =

                getNaiveWeek(#date(thisYear, 12, 28)),

 

            weekYear =

                if

                    nwn < 1

                then

                    priorYear

                else

                    if

                        nwn > lastWeekOfThisYear

                    then

                        thisYear + 1

                    else

                        thisYear,

 

            weekNumber =

                if

                    nwn < 1

                then

                    lastWeekOfPriorYear

                else

                    if

                        nwn > lastWeekOfThisYear

                    then

                        1

                    else

                        nwn,

 

            week_dateString =

                Text.PadStart(

                    Text.From(

                        Number.RoundDown(weekNumber)

                    ),

                    2,

                    "0"

                )

        in

            Text.From(weekYear) & "-W" & week_dateString & "-" & Text.From(getDayOfWeek(someDate))

in

    getISO8601Week

 

ISO Week → Date

/*

based on https://en.wikipedia.org/wiki/ISO_week_date#Calculating_a_date_given_the_year.2C_week_number_and_weekday

format input like: `2017-W02-7`

homepage: <https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca >

*/

 

let

    getDateFromISO8601Week = (inputIsoWeek as text) as date =>

        let

            getDayOfWeek = (d as date) =>

                let

                    result = 1 + Date.DayOfWeek(d, Day.Monday)

                in

                    result,

 

            isoWeekYear = Number.FromText(

                Text.Range(inputIsoWeek, 0, 4)

            ),

 

            isoWeek = Number.FromText(

                Text.Range(inputIsoWeek, 6, 2)

            ),

 

            isoWeekDay = Number.FromText(

                Text.Range(inputIsoWeek, 9, 1)

            ),

 

            // this doesn't seem right...

            weekdayOfJan4th = getDayOfWeek(#date(isoWeekYear, 1, 4)),

 

            // this doesn't seem right...

            daysInYear = Date.DayOfYear(#date(isoWeekYear, 12, 31)),

 

            // this doesn't seem right...

            daysInPriorYear = Date.DayOfYear(#date(isoWeekYear - 1, 12, 31)),

 

            correction = weekdayOfJan4th + 3,

 

            ordinal = isoWeek * 7 + isoWeekDay - correction,

 

            adjustedOrdinal =

                if

                    ordinal < 1

                then

                    ordinal + daysInPriorYear

                else

                    if

                        ordinal > daysInYear

                    then

                        ordinal - daysInYear

                    else

                        ordinal,

 

            calendarYear =

                if

                    ordinal < 1

                then

                    isoWeekYear - 1

                else

                    if

                        ordinal > daysInYear

                    then

                        isoWeekYear + 1

                    else

                        isoWeekYear,

 

            theDate = Date.AddDays(#date(calendarYear, 1, 1), adjustedOrdinal - 1)

        in

            theDate

in

    getDateFromISO8601Week

 

view raw power-query-iso-week-year.md hosted with by GitHub

Написать такую функцию не очень сложно, но приятно же, когда это уже сделали за вас?