Бюджетирование величины по календарю в 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:

p1.png

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

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

p2.png

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

p3.png

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

p4.png

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

p5.png

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

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

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

p6.png

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

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

p7.png

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

--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 знаком доллара

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

p8.png

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

p9.png

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

Следующая статья
Предыдущая статья

Комментирование закрыто.

Подпишитесь на нашу рассылку, чтобы не пропустить новые статьи!

Подписка на рассылку NeedForData

Присоединяйтесь к нам на Facebook:
Бесплатная шпаргалка по визуализации
Плакат Как что визуализировать

Плакат Как что визуализировать

© 2017    NeedForData