Рабочие и выходные дни в Power BI - создаём крутой календарь

Если вы читаете эту статью, значит столкнулись с проблемой сегрегации рабочих, выходных и праздничных дней в календаре Power BI. Мы расскажем о трёх способах решения проблемы: 
- с помощью Power Query, 
- с помощью DAX,
- путём получения данных со стороннего сайта.

Прокачка календаря в Power Query

Для деления набора дат на рабочие и выходные вы можете присвоить каждому значению дат номер дня недели. В функции Date.DayOfWeek ( ) нумерация начинается с понедельника, который считается за 0 день. Затем по этим номерам распределить 0-4 на рабочие и 5-6 на выходные:

DAX-сегрегация календаря в Power BI

Решать задачу в DAX - не самое оптимальное решение в плане траты ресурсов, но оно имеет право существовать. Ниже приведен код, который сначала вычисляет номер дня недели. Здесь нумерация идет уже от 1 до 7 и для указания начала недели с понедельника необходимо проставить аргумент 2 в функцию WEEKDAY ( ). Затем, с помощью оператора IF ( ) мы разделим результаты:

Получаем рабочие дни в календарь Power BI по API 

Существует оптимальный способ получения не только стандартного разделения дней, но и учет государственных праздников и всего производственного календаря в целом. Для этого мы будем подключаться по API запросу к сайту Консультант Плюс.

Анонсы всех видео, статей и полезностей - в нашем Telegram-канале🔥
Присоединяйтесь, обсуждайте и автоматизируйте!

Есть несколько ключевых моментов такого коннекта:

1. Года настраиваются в параметрах запросов. Стартовый год не должен быть больше конечного.
2. Год нужно задавать целыми числами. Минимально доступный год на сайте - 2010.
3. Если в заданный период попадут года, по которым нет данных, то ошибок не будет, но для них не будет сгенерировано дат.

Сначала создадим параметр начала сбора данных:

Далее, нам нужно создать таблицу-пример, по подобию которой будет подтягиваться информация с сайта. Создаем новый пустой запрос и вставляем в расширенном редакторе этот код:

let
   TextYearParam = Text.From(YearParam)&(if YearParam=2020 then "b" else ""),
    Source = Lines.FromBinary(Web.Contents("http://www.consultant.ru/law/ref/calendar/proizvodstvennye",[RelativePath = Text.From(TextYearParam) & "/?"])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.StartsWith([Column1], "#(tab)#(tab)#(tab)<td class=")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","</tr><tr>","",Replacer.ReplaceText,{"Column1"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "#Month", 1, 1),
    #"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Column1", each Text.Split(_,"</td><td class="), type text}}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Prefix", "Column1"),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each not Text.Contains([Column1], "inactively")),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows1","#(tab)#(tab)#(tab)<td class=","",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Column1", Splitter.SplitTextByEachDelimiter({">"}, QuoteStyle.Csv, false), {"Day type", "Day"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Day", each Text.BeforeDelimiter(_, "<"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Day", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each YearParam, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each #date([Year],[#"#Month"],[Day]), type date),
    #"Inserted Month Name" = Table.AddColumn(#"Added Custom1", "Month", each Date.MonthName([Date]), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day name", each Date.DayOfWeekName([Date]), type text)
in
   #"Inserted Day Name"

Создание функции сбора данных

Нам также понадобится функция для вызова сборы данных. Ее можно создать щелкнув правой кнопкой мыши по таблице-примеру ExampleOfFunction и выбрав пункт меню «Функция»:

После этого создадим еще два параметра «старт» и «конец» списка годов аналогично тому, как создавали параметр сбора данных:

Загрузка данных

Вводим в параметр StartYear год, с которого вы хотите начать свой календарь, а параметр EndYear можно оставить равным 2025. 

Чтобы вызвать функцию за период от StartYear до EndYear вставляем следующий код:

let
   Список = {StartYear..EndYear},
   #"Преобразовано в таблицу" = Table.FromList(Список, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Добавлен пользовательский объект" = Table.AddColumn(#"Преобразовано в таблицу", "Календарь", each CalendarYear([Column1])),
   #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"Column1"}),
   #"Развернутый элемент Календарь" = Table.ExpandTableColumn(#"Удаленные столбцы", "Календарь", {"Day type", "Day", "#Month", "Year", "Date", "Month", "Day name"}, {"Day type", "Day", "#Month", "Year", "Date", "Month", "Day name"})
in
   #"Развернутый элемент Календарь"

После выполнения видим полный календарь за весь период:

Как вы видите, в этом календаре не только деление на рабочие и выходные дни, но также предпраздничные и праздничные нерабочие дни.