Блог Effema
Power BI

Производственный календарь в Power BI


Разберём, как загрузить в 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"


Подробная инструкция так же в нашем видео!





Comments powered by Agentima