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