Разберём, как загрузить в Power BI производственный календарь для последующего использования. В статье показан один из вариантов, далеко не единственный. В качестве источника данных воспользуемся набором на Портале открытых данных РФ (https://data.gov.ru/opendata/7708660670-proizvcalendar). Раскроем паспорт набора:
Запустим 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:
#"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-часовой рабочей неделе"}),
#"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}}),
#"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}}),
© 2019-2021 Effema LLCПодписка на рассылку мероприятия от компании EffemaНажимая кнопку "Подписаться", Вы соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Перейти к оплате", Вы соглашаетесь на обработку персональных данных.
Купить урок по Сводным таблицам в ExcelНажимая кнопку "Перейти к оплате", Вы соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Перейти к оплате", Вы соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Перейти к оплате", Вы соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Перейти к оплате", Вы соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Перейти к оплате", Вы соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Оформить рассрочку", Вы подтверждаете, что уведомлены об условиях предоставления рассрочки и соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Оформить рассрочку", Вы подтверждаете, что уведомлены об условиях предоставления рассрочки и соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Оформить рассрочку", Вы подтверждаете, что уведомлены об условиях предоставления рассрочки и соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Оформить рассрочку", Вы подтверждаете, что уведомлены об условиях предоставления рассрочки и соглашаетесь на обработку персональных данных.
Записаться на курсНажимая кнопку "Оформить рассрочку", Вы подтверждаете, что уведомлены об условиях предоставления рассрочки и соглашаетесь на обработку персональных данных.
