Разберём, как загрузить в Power BI производственный календарь для последующего использования. В статье показан один из вариантов, далеко не единственный.
В качестве источника данных воспользуемся набором на Портале открытых данных РФ (https://data.gov.ru/opendata/7708660670-proizvcalendar).
Раскроем паспорт набора:
Набор представляет собой производственный календарь c 1999 до 2025 года. Звездочкой (*) отмечены предпраздничные (сокращенные) дни. Плюсом (+) отмечены перенесенные выходные дни.
Для загрузки в Power BI скопируем ссылку на набор (https://data.gov.ru/opendata/7708660670-proizvcalendar/data-20191112T1252-structure-20191112T1247.csv):
Запустим Power BI. Воспользуемся коннектором для получения данных с Web-страницы (вкладка Home - Get data - Web):
В открывшемся диалоге вставим скопированную ранее ссылку на набор:
Предварительный просмотр данных показывает, что в таком виде данными пользоваться будет крайне сложно. Обработаем данные в Power Query.
Загруженный набор данных в каждой строке хранит описание для одного года - сначала на каждый месяц - через запятую список дней (выходных, сокращённых и дополнительных выходных), затем несколько статистических столбцов.
Удалим статистические столбцы. Они нам не пригодятся. Наша цель - таблица, где на каждую дату будет признак - рабочий, выходной или сокращённый рабочий (последний вариант - опциональный, такие дни можно тоже интерпретировать как рабочие, в зависимости от задачи):
Итого, у нас осталось 13 колонок - колонка с годом и с каждый месяцем. Развернём колонки с месяцами в плоский список. Для этого воспользуемся командой Unpivot Columns:
Команда Unpivot Columns создаёт пары для каждого сочетания названия столбца и ячейки в этом столбце и превращает их в столбцы:
Далее разделим столбец с днями на отдельные столбцы с помощью команды Split Column:
В качестве разделителя укажем запятую, разделим по каждому вхождения разделителя:
Получим 16 столбцов с числами - столько, сколько их значится в максимуме в некоторых месяцах. Там, где значений в ячейке было менее 16 - лишние столбцы остались со значением null:
Произведём с числами ту же операцию, которую произвели с месяцами - развернём в плоский список с помощью команды Unpivot Columns:
И получим таблицу, где каждая строка - характеристики одной даты. Столбец Attribute.1 появился, как порядковый номер конкретного числа в ячейке, он не пригодится. Удалим его:
Теперь мы можем обработать звёздочки и плюсики, присвоив каждой строке (= каждой дате) некоторый признак. Чтобы далее использовать вычисление, определяющее вхождение символов * или + в строку - нам необходимо преобразовать столбец в текст:
Далее добавляе условный столбец, который будет присваивать признак каждому дню в зависимости от вхождения * и +:
Пропишем условие определения признака следующим образом: если в значении дня (столбец Value) присутствует символ *, то считаем такой день сокращённым рабочим, иначе - выходным. Мы могли бы добавить ещё одно условие для вхождения символа +, но такие дни тоже являются выходными, хоть и добавленными. Можно выделить это тип дней отдельно, если этого требует задача. Мы выделять не будем:
Получаем вот такой столбец:
Теперь можно избавить из дополнительных символов у числа (* и +), чтобы перейти к дальнейшим шагам обработки. Для удаления символов воспользуемся командой Replace Values:
Заменим звёздочку на “ничего”. Оставим поле с итоговым значением пустым. Это будет равносильно тому, чтобы удалить выбранные подстроки (в данном случае - звёздочки):
Проделаем аналогичную операцию для удаления плюсиков. Теперь у нас есть три столбца - части даты. Соберём их в дату с помощью вычисляемого столбца:
Сбор даты из элементов можно произвести несколько способами. Рассмотрим один из вариантов - соберём все элементы даты в текстовую строку и преобразуем в дату. Date.From - преобразовывает текст в дату. Text.Combine - объединяет несколько текстовых строк в одну. Text.From - преобразуем нетекстовое значение к текст. Точки между элементами даты добавим для разделения (можно воспользоваться другим символом, например, тире):
Date.From(
Text.Combine(
{
[Value],
".",
[Attribute],
".",
Text.From([#"Год/Месяц"])
}
)
)
В результате получим столбец с датой - Date. Теперь можем удалить составные столбцы - они свою роль сыграли:
Осталось скорректировать типы данных. Поставить, что столбец Date - это данные типа Date, Day type - Text:
Результат:
Итоговый код обработки данных:
let
Source = Csv.Document(Web.Contents("https://data.gov.ru/opendata/7708660670-proizvcalendar/data-20191112T1252-structure-20191112T1247.csv"),[Delimiter=",", Columns=18, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Год/Месяц", Int64.Type}, {"Январь", type text}, {"Февраль", type text}, {"Март", type text}, {"Апрель", type text}, {"Май", type text}, {"Июнь", type text}, {"Июль", type text}, {"Август", type text}, {"Сентябрь", type text}, {"Октябрь", type text}, {"Ноябрь", type text}, {"Декабрь", type text}, {"Всего рабочих дней", Int64.Type}, {"Всего праздничных и выходных дней", Int64.Type}, {"Количество рабочих часов при 40-часовой рабочей неделе", Int64.Type}, {"Количество рабочих часов при 36-часовой рабочей неделе", type text}, {"Количество рабочих часов при 24-часовой рабочей неделе", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Всего рабочих дней", "Всего праздничных и выходных дней", "Количество рабочих часов при 40-часовой рабочей неделе", "Количество рабочих часов при 36-часовой рабочей неделе", "Количество рабочих часов при 24-часовой рабочей неделе"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Год/Месяц"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7", "Value.8", "Value.9", "Value.10", "Value.11", "Value.12", "Value.13", "Value.14", "Value.15", "Value.16"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}, {"Value.4", type text}, {"Value.5", type text}, {"Value.6", type text}, {"Value.7", type text}, {"Value.8", type text}, {"Value.9", type text}, {"Value.10", type text}, {"Value.11", type text}, {"Value.12", type text}, {"Value.13", Int64.Type}, {"Value.14", Int64.Type}, {"Value.15", Int64.Type}, {"Value.16", Int64.Type}}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"Год/Месяц", "Attribute"}, "Attribute.1", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute.1"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Value", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Day type", each if Text.Contains([Value], "*") then "Сокращённый рабочий" else "Выходной"),
#"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column","*","",Replacer.ReplaceText,{"Value"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","+","",Replacer.ReplaceText,{"Value"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Date", each Date.From(
Text.Combine(
{
[Value],
".",
[Attribute],
".",
Text.From([#"Год/Месяц"])
}
)
)),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Год/Месяц", "Attribute", "Value"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns2",{{"Date", type date}, {"Day type", type text}})
in
#"Changed Type3"
*****
Получили почти то, что хотели, за исключением одного “но” - в итоговой таблице нет рабочих дней. Для решения некоторых задач полученного результата вполне достаточно. Кроме того, обработку дней мы можем далее заложить логикой (а-ля “Всё, что не в таблице - рабочие дни”). Но - закончим задачу для полноценного календаря, где тип присвоен каждой дате. Для этого сгенерим таблицу с полным список дат и объединим с полученной. Для генерации списка дат создадим пустой запрос:
И отредактируем код запроса вручную в Расширенном редакторе (Advanced Editor):
Зададим границы диапазона данных - дату начала и дату окончания:
date_start = #date(2015, 1, 1),
date_end = #date(2023, 1, 1),
Воспользуемся List.Dates для генерации списка дат. Ей необходимо три параметра: дата старта диапазона, количество элементов и шаг:
date_list = List.Dates(
date_start,
Duration.Days(date_end - date_start) + 1,
#duration(1, 0, 0, 0)
)
Получим вот такой код:
let
date_start = #date(2015, 1, 1),
date_end = #date(2023, 1, 1),
date_list = List.Dates(
date_start,
Duration.Days(date_end - date_start) + 1,
#duration(1, 0, 0, 0)
)
in
date_list
И в результате получим вот список дат в заданном диапазоне. Это список (не таблица) и, соответственно, не имеет всех атрибутов таблицы. Преобразуем его в таблицу:
А вот это уже таблица. Переименуем, изменим типа данных на Date:
Получаем таблицу со списком дат, но без указания типов этих дат. “Вытащим” типы данных из первой созданной таблицы. Для этого объединим таблицы:
Если мы вызвали команду объединения, находясь во второй таблице, то эта вторая таблица автоматически встанет в качестве первой объединяемой таблицы. Нам нужно выбрать вторую - это данные, загруженные с Портала открытых данных. Необходимо указать, по каким столбцам сопоставлять данные - выбираем в обеих таблицах колонку Date:
Для каждой строки создалась подтаблица из данных присоединённой таблицы. Развернём колонки:
Снимем галочку использования названия таблицы в качестве префикса, снимем галочкe со столбца Date:
В результате получим таблицу, у которой будут проставлены типы дат у выходных и сокращённых рабочих дней. У части дат будет null вместо типа даты. Исходя из логики загруженного производственного календаря делаем вывод, что это простые рабочие дни. Заменим null на слово “Рабочий”:
Вот теперь - всё. Осталось только загрузить :)
Итоговый код второй таблицы:
let
date_start = #date(2015, 1, 1),
date_end = #date(2023, 1, 1),
date_list = List.Dates(
date_start,
Duration.Days(date_end - date_start)+1,
#duration(1, 0, 0, 0)
),
#"Converted to Table" = Table.FromList(date_list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Date"}, #"data-20191112T1252-structure-20191112T1247", {"Date"}, "data-20191112T1252-structure-20191112T1247", JoinKind.LeftOuter),
#"Expanded data-20191112T1252-structure-20191112T1247" = Table.ExpandTableColumn(#"Merged Queries", "data-20191112T1252-structure-20191112T1247", {"Day type"}, {"Day type"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded data-20191112T1252-structure-20191112T1247",null,"Рабочий",Replacer.ReplaceValue,{"Day type"})
in
#"Replaced Value"
