Блог Needfordata
Excel

Бюджетирование величины по календарю в Power Query

Бюджетирование величины по календарю в Power Query



Все, кто сегодня занимается бизнес-анализом, да и любой, изучающий Excel, рано или поздно приходят на сайт Bill Jelen (Билл Джелен, он же Mr Excel). У него есть очень интересный подкаст о разделении стоимости контракта на N месяцев. Интересен он не только тем, что подобные задачи встречались при работе с реальными клиентами, но и потому, что отбор нужных данных в Power Query — то, о чём мы часто пишем в своих статьях.



image
В этой статье мы собираемся взять те же данные, что и в подкасте Bill и Mike Girvin (Майк Гирвин — автор книги Формулы массивов в Excel), и показать как получить аналогичные с ними результаты в Power Query и Power Pivot.


Для начала, сделаем в Excel две таблицы — Contract и Month:



Конечно, это не тот же макет, что в подкасте, но так сделано сознательно. Мы хотим держать отдельно источник данных и результат (он может быть PivotTable, куб-формулами или таблицей Power View).



Далее импортируем таблицу Month table в Power Query, используя кнопку From Table, и добавляем столбец индексов, выбрав пункт Add Index. Должно получиться так:



Нам не нужно никуда загружать эту таблицу, хотя результат понадобится в следующем запросе. Поэтому снимем оба флажка секции Load Settings в Query Editor и вернёмся к листу:



Импортируем таблицу Contract и добавляем столбец индексов:



Теперь добавляем пользовательский столбец, в котором определяем ежемесячную сумму, разделив столбец Contract Amount на Months In Contract:



Мы подобрались к интересному моменту. Вставляем ещё один пользовательский столбец и вводим такой код для него:



Table.FirstN (Month, [Months In Contract])



Каждая строка этого столбца содержит первые N строк таблицы Month, где N выбирается из столбца [Months In Contract]. На выходе получим:



Нажмём на значок "Развернуть" в заголовке столбца, чтобы повторить строки контракта на соответствующее количество месяцев.



Переименуем столбцы, установим типы данных и можно загружать результат в модель данных эксель.



Весь код для обоих запросов:

--Month Query

let

Source = Excel. CurrentWorkbook (){[Name="Month"]}[Content],

InsertedIndex = Table. AddIndexColumn (Source,"Index"),

ReorderedColumns = Table. ReorderColumns (InsertedIndex,{"Index", "Month"})

in

ReorderedColumns

--Contract Query

let

Source = Excel. CurrentWorkbook (){[Name="Contract"]}[Content],

InsertedIndex = Table. AddIndexColumn (Source,"Index"),

RenamedColumns = Table. RenameColumns (InsertedIndex,{{"Index", "ContractID"}}),

ReorderedColumns = Table. ReorderColumns (RenamedColumns,{"ContractID",

"Months In Contract", "Contract Amount"}),

InsertedCustom = Table. AddColumn (ReorderedColumns, "Allocated Amount",

each [Contract Amount]/[Months In Contract]),

InsertedCustom1 = Table. AddColumn (InsertedCustom, "Custom",

each Table. FirstN (Month, [Months In Contract])),

# "Expand Custom" = Table. ExpandTableColumn (InsertedCustom1, "Custom",

{"Index", "Month"}, {"Custom.Index", "Custom.Month"}),

RenamedColumns1 = Table. RenameColumns (#"Expand Custom",{{"Custom.Index", "MonthID"},

{"Custom.Month", "Month"}}),

ChangedType = Table. TransformColumnTypes (RenamedColumns1,{{"Allocated Amount", type number},

{"Contract Amount", type number}, {"MonthID", type number},

{"Months In Contract", type number}, {"ContractID", type number}})

in

ChangedType



Наконец перейдём к окну Power Pivot и сделаем две вещи:

    Отсортируем столбец Month по столбцу MonthID





    Отформатируем столбец Allocated Amount знаком доллара





    Готово! Теперь можно создать сводную таблицу с нужными данными:



    Может быть, это более длинный путь, чем в примерах Билла или Майка, но вряд ли сложнее. К тому же, наши данные в модели данных Excel позволяют делать их презентацию более гибкой. Например, можно воспользоваться Power View без необходимости что-либо менять в самой модели или формулах:



    Авторы отошли от устоявшейся практики отделять таблицу дат от Power Pivot, чтобы не уходить от изначальных примеров.
    Made on
    Tilda