Бюджетирование величины
по календарю в Power Query
Текст представляет собой адаптированный перевод статьи Криса Вебба, оригинал – Allocation in Power Query.
Рассматривается англоязычный Power Query.
Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И это одна из них.

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

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

В этой статье мы собираемся взять те же данные, что и в подкасте 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, чтобы не уходить от изначальных примеров.