Создание измерения дат с помошью Power Query

В Power Query есть несколько классов, с помощью которых можно не только извлекать данные или осуществлять какие-либо преобразования, но и генерировать поток данных. Например можно легко заполнить последовательность из чисел или сгенерировать диапазон дат.

Для начала создадим пустой шаблон запроса Power Query.

image1

Теперь нам нужно написать саму функцию, в которую мы будем передавать диапазон дат и она нам вернет данные для измерения. Перейдем на вкладку View и откроем Advanced Editor.

image2

Здесь мы можем писать текст нашей функции, который выглядит следующим образом.

let
DimDate = (StartDate as date, optional EndDate as date) =>
let
EndDate = if EndDate is null then DateTime.Date(Date.EndOfYear(Date.AddYears(DateTime.FixedLocalNow(), 1))) else EndDate,
MonthNames = { «Январь», «Февраль», «Март», «Апрель», «Май″, «Июнь», «Июль», «Август», «Сентябрь», «Октябрь», «Ноябрь», «Декабрь» },
DayOfWeekNames = { «Понедельник», «Вторник», «Среда», «Четверг″, «Пятница», «Суббота», «Воскресенье» },
DateList = List.Dates(StartDate, Duration.TotalDays(EndDate — StartDate) + 1, #duration(1,0,0,0)),
DateID = Table.FromList(DateList, Splitter.SplitByNothing(), { «DateID» }),
DateName = Table.AddColumn(DateID, «DateName», each Date.ToText([DateID], «dd.MM.yyyy», «ru-RU»)),
YearID = Table.AddColumn(DateName, «YearID», each Date.Year([DateID])),
YearName = Table.AddColumn(YearID, «YearName», each Number.ToText([YearID]) & » г″),
QuarterOfYearID = Table.AddColumn(YearName, «QuarterOfYearID», each Date.QuarterOfYear([DateID])),
QuarterOfYearName = Table.AddColumn(QuarterOfYearID, «QuarterOfYearName», each Number.ToText([QuarterOfYearID]) & » квартал»),
QuarterID = Table.AddColumn(QuarterOfYearName, «QuarterID», each [YearID] * 10 + Date.QuarterOfYear([DateID])),
QuarterName = Table.AddColumn(QuarterID, «QuarterName», each Number.ToText([YearID]) & » / » & Number.ToText([QuarterOfYearID]) & » квартал»),
MonthOfYearID = Table.AddColumn(QuarterName, «MonthOfYearID», each Date.Month([DateID])),
MonthOfYearName = Table.AddColumn(MonthOfYearID, «MonthOfYearName», each MonthNames{[MonthOfYearID] — 1}),
MonthID = Table.AddColumn(MonthOfYearName, «MonthID», each [YearID] * 100 + Date.Month([DateID])),
MonthName = Table.AddColumn(MonthID, «MonthName», each Number.ToText([YearID]) & » / » & MonthNames{[MonthOfYearID] — 1}),
WeekOfYearID = Table.AddColumn(MonthName, «WeekOfYearID», each Date.WeekOfYear([DateID])),
WeekOfYearName = Table.AddColumn(WeekOfYearID, «WeekOfYearName», each Number.ToText([WeekOfYearID]) & » неделя»),
WeekID = Table.AddColumn(WeekOfYearName, «WeekID», each [YearID] * 100 + Date.WeekOfYear([DateID])),
WeekName = Table.AddColumn(WeekID, «WeekName», each Number.ToText([YearID]) & » / » & Number.ToText([WeekOfYearID]) & » неделя»),
DayOfWeekID = Table.AddColumn(WeekName, «DayOfWeekID», each if Date.DayOfWeek([DateID]) = 0 then 7 else Date.DayOfWeek([DateID])),
DayOfWeekName = Table.AddColumn(DayOfWeekID, «DayOfWeekName», each DayOfWeekNames{[DayOfWeekID] — 1})
in
DayOfWeekName
in
DimDate

Именуем наш запрос как DimDate.

image3

Убираем галочку Load to worksheet, т.к. мы реализовали повторно используемую функцию, а не сам запрос, возвращающий результат в Excel.

image4

После чего на вкладке Home жмем Apply & Close, чтобы сохранить нашу функцию.

image5

Теперь все готово для того, чтобы ей воспользоваться. Создаем еще один пустой запрос и в Advanced Editor пишем следующее.

let
Source = DimDate(#date(2014,1,1), #date(2014,1,31))
in
Source

Первый параметр представляет собой дату первого элемента измерения, второй (необязательный) конечную дату. Если второй параметр не будет указан, то за конечную дату будет принято 31 декабря следующего за текущим календарного года.

Именуем наш запрос как нам нравится и нажимаем Apply & Close после чего увидим в Excel следующий результат.

image6

Полученную таблицу Excel можно использовать как источник данных для измерения дат, например в Power Pivot.

Напоследок будет приведен пример измерения времени.

let
TimeList = List.Transform(List.DateTimes(#datetime(1,1,1,0,0,0), 1440, #duration(0,0,1,0)), each DateTime.Time(_)),
TimeID = Table.FromList(TimeList, Splitter.SplitByNothing(), { «TimeID» }),
MinuteID = Table.AddColumn(TimeID, «MinuteID», each Time.Minute([TimeID])),
MinuteName = Table.AddColumn(MinuteID, «MinuteName», each Time.ToText([TimeID], «HH:mm»)),
HourID = Table.AddColumn(MinuteName, «HourID», each Time.Hour([TimeID])),
HourName = Table.AddColumn(HourID, «HourName», each Time.ToText([TimeID], «HH:00″)),
DayPartID = Table.AddColumn(HourName, «DayPartID», each if [HourID] >= 0 and [HourID] <= 5 then 1 else if [HourID] >= 6 and [HourID] <= 11 then 2 else if [HourID] >= 12 and [HourID] <= 17 then 3 else if [HourID] >= 18 and [HourID] <= 23 then 4 else null),
DayPartName = Table.AddColumn(DayPartID, «DayPartName», each if [DayPartID] = 1 then «Ночь» else if [DayPartID] = 2 then «Утро» else if [DayPartID] = 3 then «День» else if [DayPartID] = 4 then «Вечер» else null)
in
DayPartName

0  

Вышла первая стабильная версия Power Query

Недавно компанией Microsoft была выпущена первая стабильная версия Power Query, которая датируется февралем 2014 и имеет номер версии 2.10.3547.461. Все предыдущие версии носили статус Preview.

Power Query – это довольно интересный инструмент, надстройка для Excel, которая позволяет подключаться к различным источникам и получать данные из этих источников, а в дальнейшем осуществлять различные действия с ними. Но на этом функциональные возможности Power Query не исчерпываются. Кроме всего прочего имеется визуальный редактор запросов и встроенный язык программирования, который позволяет существеено расширить сферу применения данного инструмента.

Power Query умеет подключаться к различным СУБД (SQL Server, Oracle, Sybase, DB2, MySQL, Access), документам в формате Excel, CSV, JSON, XML, HTML спискам SharePoint, Hadoop-кластерам, веб-сервисам Azure Marketplace, любым файлам на диске или в сети, в том числе по протоколу HTTP и т.д.

Скачать свежую версию Power Query можно по ссылке.

Документация доступна по ссылкам:

Power Query Formula Language Specification

Power Query Formula Library Specification

0