Post-Image

Работа со временем

Рвав-рвав, собака Смайл бренчит железками!

Сегодня в программе “адская смесь”, а именно: пользовательские функции Power Query (пытаемся развиваться в новом направлении), редактирование кода Power Query посредством расширенного редактора, ну и DAX, само собой.

Пример:

  • Таблица “Перечень задач” содержит одну строку с названием задачи, а также датах ее начала и окончания в кодировке “Unix-time”:

    art_017_screen_1

    Задача:

    На основе представленных данных необходимо осуществить следующую трансформацию данных:

  • Перевести время в более понятный конечному пользователю вид, а именно, в UTC (GMT).

  • Продемонстрировать дальнейшую трансформацию полученного значения, применив нужный часовой пояс (например, получив Московское время).

  • Рассчитать длительность временного промежутка в формате “день/время”.

    Процесс разработки:

  • Поскольку легких путей мы тут не ищем, сначала необходимо реализовать пару пользовательских функций для трансформации базовых значений в привычный конечный вид “дата/время”. Пример построения пользовательской функции был рассмотрен совсем недавно (см. статью Различные способы достижения цели в MS Power BI).

    Функция, трансформирующая время UNIX во время UTC выглядит следующим образом:

    art_017_screen_2

    Рвав-рвав, дабы не занимать много места, а также в целях предотвращения бездумного копирования, пример функции представлен картинкой, как и дальнейший код расширенного редактора. Можете ругаться на “вредного сабакена”, но тема написания пользовательских функций, на мой взгляд, не является простой, и после ручного повторения у читателей больше останется в голове.

    Также в код добавлен небольшой комментарий, который тоже желательно бы прочитать.

  • Функция, осуществляющая перевод времени UTC в нужный часовой пояс (Московское время) выглядит следующим образом:

    art_017_screen_3

    Рвав-рвав, конечно, мы могли бы обойтись и одной функцией, однако дальновидный собакен подумал, что, возможно вам пригодится только 2-я функция, а если в нее еще и зашить параметр часового пояса, а не статичное значение (+3 часа), то она станет универсальной, не придется каждый раз “работать руками”.

  • Далее необходимо последовательно применить указанные функции к нашим данным, однако тут имеется следующая проблема: при использовании встроенной кнопки “Вызвать настраиваемую функцию” мы получим новый дополнительный столбец, а никак не преобразование текущих данных. Поэтому здесь придется осуществить ручное редактирование запроса посредством расширенного редактора:

    art_017_screen_4

    Рвав-рвав, добавленные шаги выделены желтым цветом, и написаны различным образом. Конечно, можно было бы просто последовательно вызывать обе функции и применять их к столбцам “Начало задачи” и “Окончание задачи”, а затем просто удалить оригинальные столбцы, но, как уже говорилось выше, в данной статье это не наш метод…

  • В результате описанных действий первоначальные данные будут преобразованы в следующий вид:

    art_017_screen_5

  • Для того, чтобы рассчитать длительность временного промежутка в формате “день/время”, при создании расчетного столбца можно применить следующую формулу:

    Длительность (день/время) = 
    VAR _Difference = 'Перечень задач'[Окончание задачи] - 'Перечень задач'[Начало задачи]
    RETURN
        FORMAT ( INT ( _Difference ), "00" ) & " дн." & FORMAT ( _Difference, " hh:mm:ss" )

    Рвав-рвав, признаюсь, данный способ был подсмотрен любопытствующим собакеном как решение, альтернативное собственной придумке, заключающейся в выделении целой части дня, часа и пр. На мой взгляд, это весьма элегантное решение само по себе, а также гораздо менее трудоемкое, так как функция DATEDIFF, вычисляющая промежутки между датами, использует один временной отрезок (месяцы, дни и пр).

  • Последним шагом является проверка получившегося результата, а также, для получения большей уверенности в правильности трансформации данных, можете воспользоваться каким-нибудь конвертором, проверив вручную еще несколько значений:

    art_017_screen_6

    Рвав-рвав, “вот и сказочки конец”, всем развития!

    Ваш Смайл