Набор функций для Power Query для создания производственного календаря 2015-2022 года на базе MS Excel / MS Power BI.
holyday_date.pq: Функция для проверки даты на праздничные и выходные дни.
(check_date as date) =>
let
//check_date = #date(2021, 9, 6),
non_work_days = {
#date(2015,1,1),
#date(2015,1,2),
#date(2015,1,5),
#date(2015,1,6),
#date(2015,1,7),
#date(2015,1,8),
#date(2015,1,9),
#date(2015,2,23),
#date(2015,3,9),
#date(2015,5,1),
#date(2015,5,4),
#date(2015,5,11),
#date(2015,6,12),
#date(2015,11,4),
#date(2016,1,1),
#date(2016,1,4),
#date(2016,1,5),
#date(2016,1,6),
#date(2016,1,7),
#date(2016,1,8),
#date(2016,2,22),
#date(2016,2,23),
#date(2016,3,7),
#date(2016,3,8),
#date(2016,5,2),
#date(2016,5,3),
#date(2016,5,9),
#date(2016,6,13),
#date(2016,11,4),
#date(2017,1,2),
#date(2017,1,3),
#date(2017,1,4),
#date(2017,1,5),
#date(2017,1,6),
#date(2017,2,23),
#date(2017,2,24),
#date(2017,3,8),
#date(2017,5,1),
#date(2017,5,8),
#date(2017,5,9),
#date(2017,6,12),
#date(2017,11,6),
#date(2018,1,1),
#date(2018,1,2),
#date(2018,1,3),
#date(2018,1,4),
#date(2018,1,5),
#date(2018,1,8),
#date(2018,2,23),
#date(2018,3,8),
#date(2018,3,9),
#date(2018,5,1),
#date(2018,5,2),
#date(2018,5,9),
#date(2018,6,11),
#date(2018,6,12),
#date(2018,11,5),
#date(2019,1,1),
#date(2019,1,2),
#date(2019,1,3),
#date(2019,1,4),
#date(2019,1,7),
#date(2019,1,8),
#date(2019,3,8),
#date(2019,5,1),
#date(2019,5,2),
#date(2019,5,3),
#date(2019,5,9),
#date(2019,5,10),
#date(2019,6,12),
#date(2019,11,4),
#date(2020,1,1),
#date(2020,1,2),
#date(2020,1,3),
#date(2020,1,6),
#date(2020,1,7),
#date(2020,1,8),
#date(2020,2,24),
#date(2020,3,9),
#date(2020,5,1),
#date(2020,5,4),
#date(2020,5,5),
#date(2020,5,11),
#date(2020,6,12),
#date(2020,11,4),
#date(2021,1,1),
#date(2021,1,4),
#date(2021,1,5),
#date(2021,1,6),
#date(2021,1,7),
#date(2021,1,8),
#date(2021,2,22),
#date(2021,2,23),
#date(2021,3,8),
#date(2021,5,3),
#date(2021,5,10),
#date(2021,6,14),
#date(2021,11,4),
#date(2021,11,5),
#date(2021,12,31),
#date(2022,1,3),
#date(2022,1,4),
#date(2022,1,5),
#date(2022,1,6),
#date(2022,1,7),
#date(2022,2,23),
#date(2022,3,7),
#date(2022,3,8),
#date(2022,5,2),
#date(2022,5,3),
#date(2022,5,9),
#date(2022,5,10),
#date(2022,6,13),
#date(2022,11,4),
#date(2022,12,31)
},
non_work_table = Table.AddColumn(
Table.FromList(
non_work_days,
Splitter.SplitByNothing(),
{"Дата"},
null,
ExtraValues.Error
),
"Тип дня",
each Date.DayOfWeekName([Дата])
),
work_days = {
#date(2016,2,20),
#date(2018,4,28),
#date(2018,6,9),
#date(2018,12,29),
#date(2021,2,20),
#date(2022,3,5)
},
work_table = Table.AddColumn(
Table.FromList(
work_days,
Splitter.SplitByNothing(),
{"Дата"},
null,
ExtraValues.Error
),
"Тип дня",
each Date.DayOfWeekName([Дата])
),
result = (
if Date.DayOfWeek(check_date) < 5 then
if List.Contains(non_work_days, check_date) then "выходной"
else "рабочий"
else
if List.Contains(work_days, check_date) then "рабочий"
else "выходной"
)
in
result
next_work_date.pq: Функция определения следующего рабочего дня
(check_date as date) =>
let
//check_date = #date(2021,9,4),
next_day = Date.AddDays(check_date,1),
result = (
if holyday_date(check_date) = "рабочий" then check_date
else if holyday_date(next_day) = "рабочий" then next_day
else @next_work_date(next_day)
),
date_result = Date.From(result)
in
date_result
