Как перестать бояться функционального программирования 2.0 или открываем новый уровень в PQ/PB
Invite pending
В первую очередь я хотел бы поблагодарить Sergey Lossev, автора статьи Power Query: как перестать бояться функционального программирования..., за подробное и качественное раскрытие темы режима расширенного редактирования запросов в Power Query и извиниться за плагиат в заголовке моей статьи.
Изящество и ловкость с которой проводился манипуляция данных в этой статье подвинула меня дальше кнопочного создания запросов, а так же более углубленно взглянуть на возможности другого механизма — DAX функций.
Раскрыть еще один вариант формирования списка дата, но уже с использованием функций языка запросов DAX
Основной задачей формирования списка дат в статье Простой план-фактный анализ... — это возможность последующей визуализации данных в качестве временной оси на графиках, сравнения с предыдущими периодами и т.д.
Логичным было сформировать список заранее и загружать в модель — что собственно я и делал до недавнего времени. Однако мне не давала покоя вкладка на ленте Power BI и кнопки на ней:
Нажав на кнопку «Создать параметр» я сформировал новую таблицу, но уже силами DAX
Функции GENERATESERIES оказалась простой, как АК-47 — Начальное значение; Конечное значение; Инкремент.
Следовательно нужно найти способ формирования этих данных в формате datetime — и это так же не оказалось сложным, в арсенале DAX была обнаружена соответствующая нашим потребностям DATE(year, month, day). Скормив GENERATESERIES начальную дату 01 января 2017 и конечную дату 05 января 2017 с шагом в 1, я получил следующую таблицу:
(как вы поняли для создания таблицы достаточно нажать соответствующую
кнопку на панели и набрать формулу в строке)
Вот вроде и все таблица с одним столбцем сформирована — согласитесь совсем не сложно.
Хотя Power BI автоматически формирует иерархию дат при использовании даты в качество оси
Я часто использую дополнительные текстовые описания, например Q'1, янв'17 и т.д.
Давайте попробуем сделать это.
Добавляем столбец и при помощи функции YEAR() — возвращаем год для даты в виде четырехзначного числа
Соответственно функции MONTH(), DAY(), WEEKDAY() возвращает в числовом формате номер месяц, дня или недели.
Но наша задача была вывести название месяца, для этого я применил функцию FORMAT() — она преобразует значение в текст в указанном формате. При помощи функции DAX CONCATENATE() для 2-х строк или обычного & объединим нужные нам строки
Вот вроде бы и все.
Думаю данный метод имеет чуть больше преимущество по сравнению с генерацией дат и календаря в PQ:
Ну и тем, кому нравиться код в одной строке
Изящество и ловкость с которой проводился манипуляция данных в этой статье подвинула меня дальше кнопочного создания запросов, а так же более углубленно взглянуть на возможности другого механизма — DAX функций.
Цель статьи
Раскрыть еще один вариант формирования списка дата, но уже с использованием функций языка запросов DAX
I. Генерируем список (таблицу) дат
Основной задачей формирования списка дат в статье Простой план-фактный анализ... — это возможность последующей визуализации данных в качестве временной оси на графиках, сравнения с предыдущими периодами и т.д.
Логичным было сформировать список заранее и загружать в модель — что собственно я и делал до недавнего времени. Однако мне не давала покоя вкладка на ленте Power BI и кнопки на ней:
Нажав на кнопку «Создать параметр» я сформировал новую таблицу, но уже силами DAX
Функции GENERATESERIES оказалась простой, как АК-47 — Начальное значение; Конечное значение; Инкремент.
Следовательно нужно найти способ формирования этих данных в формате datetime — и это так же не оказалось сложным, в арсенале DAX была обнаружена соответствующая нашим потребностям DATE(year, month, day). Скормив GENERATESERIES начальную дату 01 января 2017 и конечную дату 05 января 2017 с шагом в 1, я получил следующую таблицу:
(как вы поняли для создания таблицы достаточно нажать соответствующую
кнопку на панели и набрать формулу в строке)
Вот вроде и все таблица с одним столбцем сформирована — согласитесь совсем не сложно.
Попробуйте вместо цифрового инкремента поставить функцию TIME
II. Генерируем дополнительные столбцы
Хотя Power BI автоматически формирует иерархию дат при использовании даты в качество оси
Я часто использую дополнительные текстовые описания, например Q'1, янв'17 и т.д.
Давайте попробуем сделать это.
Добавляем столбец и при помощи функции YEAR() — возвращаем год для даты в виде четырехзначного числа
Соответственно функции MONTH(), DAY(), WEEKDAY() возвращает в числовом формате номер месяц, дня или недели.
Но наша задача была вывести название месяца, для этого я применил функцию FORMAT() — она преобразует значение в текст в указанном формате. При помощи функции DAX CONCATENATE() для 2-х строк или обычного & объединим нужные нам строки
Вот вроде бы и все.
Заключение
Думаю данный метод имеет чуть больше преимущество по сравнению с генерацией дат и календаря в PQ:
- Таблица формируется сразу в модели данных — нет нужды запускать редактор и потом ждать загрузку в модель
- Вы можете добавить в качества параметров даты которые хотите иметь начальными и конечными значениями и быстро регулировать список
Гурманам на закуску
Ну и тем, кому нравиться код в одной строке
Код
Calendar =
GENERATE (
CALENDAR ( DATE ( 2017; 1; 1 ); DATE ( 2017; 1; 5 ) );
VAR currentDay = [Date]
VAR myDay = DAY ( currentDay )
VAR myMonth = FORMAT(currentDay;"mmm")&"'"&FORMAT(currentDay;"yy")
VAR myYear = YEAR ( currentDay )
VAR myQuarter = "Q'"&FORMAT(currentDay;"q")
RETURN
ROW ( "Year"; myYear; "Quarter";myQuarter;"Month"; myMonth;"Day"; myDay)
)