Создание измерения дат с помошью 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

leave your comment


− четыре = 5