MyTetra Share
Делитесь знаниями!
Номер недели ISO в Power Query / M и Power BI
Время создания: 29.12.2021 17:30
Текстовые метки: Power Query, WeekISO, week_num
Раздел: !Закладки - MSO - Excel - Power Query
Запись: adgaver/mytetra_base_New/master/base/1640788204by1qqybv4m/text.html на raw.githubusercontent.com

Номер недели 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

Так же в этом разделе:
 
MyTetra Share v.0.58
Яндекс индекс цитирования