Работа с датами в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел 

Начнем с преобразования текста в даты. При загрузке таблицы со значениями даты или даты/времени Power Query выполняет преобразование столбцов с учетом «правильного» формата.

Рис. 1. Некоторые значения дат Excel не распознал; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате архива (внутри файлы Excel с примерами; без макросов)

Загрузите файл C02E05.xlsx с сайта или из приложенного к заметке архива и сохраните его на диске. Файл содержит каталог товаров базы AdventureWorks. В столбце G указана дата выпуска. Представьте, что разные сотрудники вводили даты в пяти форматах:

• 7/1/2018
• 2018-07-01
• 1.2018
• Jul 1, 2018
• 1 July, 2018

Откройте копию рабочей книги C02E05.xlsx. В ячейку H2 введите формулу: =G2+1. Измените формат ячейки Н2 на дату. Протяните формулу до H6 (см. рис. 1). Значения в Н2 и Н4 Excel распознал неверно, применив российские региональные настройки, действующие в Windows, к файлу, созданному на ПК с американскими настройками. Значения в Н5 и Н6 Excel не смог представить в формате даты.

Посмотрим, как с задачей справится Power Query. Создайте новую книгу Excel. Пройдите по меню Данные –> Получить данные –> Из файла –> Из книги. Выберите C02E05.xlsx. Нажмите Импорт. В окне Навигатора выберите Sheet1, нажмите Преобразовать данные. Откроется окно редактора Power Query. Обратите внимание: PQ смог распознать все значения как даты, но не все корректно:

Рис. 2. Даты, распознанные PQ с настройками по умолчанию

Это связано с тем, что региональные настройки на вашем ПК отличаются от региональных настроек в файле C02E05.xlsx. Чтобы настроить импорт пройдите в редакторе PQ по меню Файл –> Параметры и настройки –> Параметры запроса. Перейдите на вкладку Региональные настройки. Установите Языковый стандарт – Английский (США):

Рис. 3. Изменение параметров запроса

Нажмите Ok. Обновите запрос: на вкладке Главная редактора PQ кликните Обновить предварительный просмотр. Теперь даты распознаны верно. На вкладке Главная нажмите Закрыть и загрузить. Данные появятся на листе Excel.

Работа с датами при использовании двух локальных настроек

Рассмотрим ситуацию, когда даты относятся к нескольким локальным настройкам. Для простоты файл C02E06.xlsx содержит две строки. Причем даты отформатированы с учетом локальной настройки:

• Country: USA, Release Date: 7/23/2018.
• Country: United Kingdom, Release Date: 23/7/2018.

При импорте в PQ правильно будет распознана лишь та дата, которая соответствует установленной региональной настройке. Чтобы справиться с ситуацией, сначала разделим столбец с датами по разделителю, а затем создадим условный столбец.

Создайте новую книгу Excel. Пройдите по меню Данные –> Получить данные –> Из файла –> Из книги. Выберите C02E06.xlsx. Нажмите Импорт. В окне Навигатора выберите Products, нажмите Преобразовать данные. Откроется окно редактора. Выберите столбец Release Data. Пройдите по меню Преобразование –> Разделить столбец –> По разделителю. В окне Разделить столбец по разделителю в качестве разделителя уже выбрана косая черта /. Щелкните Ok.

Столбец Release Data разделиться на три столбца. Пройдите по меню Добавление столбца –> Настраиваемый столбец. В окне Настраиваемый столбец введите имя нового столбца – Date и формулу:

Рис. 4. Настраиваемый столбец

Кликните Ok. Измените тип столбца Date на Дата. Удалите столбцы Release Date.1, Release Data.2 и Release Data.3. На вкладке Главная нажмите Закрыть и загрузить. Данные появятся на листе Excel.

Рассмотрим альтернативный вариант. Продублируем текущий запрос для сохранения обеих версий. В окне Excel в области Запросы и подключения кликните правой кнопкой мыши на запрос Products. Выберите Изменить. Откроется окно редактора PQ. В левой части кликните правой кнопкой мыши на запрос Products. Выберите Дублировать. Создастся запрос Products (2). Перейдите на панель Примененные шаги и удалите последние три шага, начиная с Добавлен пользовательский объект.

Нажмите и удерживайте клавишу Ctrl и выделите три столбца в следующем порядке: Release Date.2, Release Date.1 и Release Date.3. На вкладке Добавление столбца кликните Объединить столбцы. В окне Объединить столбцы в качестве разделителя задайте Пользовательский и введите косую черту (/). В поле Имя нового столбца введите US Date, кликните Ok.

Нажмите и удерживайте клавишу Ctrl и выделите три столбца дат в ином порядке: Release Date.1, Release Date.2 и Release Date.3. Повторите процедуру, а новый столбец назовите UK Date.

На вкладке Добавление столбца выберите команду Условный столбец. В окне Добавление условного столбца установите:

Рис. 5. Настройка условного столбца

Кликните Ok. Измените формат столбца Date на Дата. Удалите столбцы Release Date.1, Release Date.2, Release Date.3, US Date и UK Date. Загрузите запрос на лист Excel. Даты отображаются в соответствии с локальными настройками Windows.

Выборка элементов даты и времени

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

Рис. 6. Меню Дата

Возраст – вычитает текущее дату/время из значения в столбце. По умолчанию возвращает число дней. Далее можно представить разницу в годах, часах, секундах и других единицах измерения.

Только дата – извлекает дату из столбца с типом Дата/Время. Вместе с преобразованием Только время (меню Время справа от меню Дата) можно разделить столбец Дата/Время на два отдельных столбца Дата и Время. Это упрощает модель, делает работу с ней гибче, уменьшает размер отчета и сокращает объем требуемой для него памяти.

Другие опции позволяют извлекать дату начала и конца периода, число дней в периоде и многое другое. При выборе нескольких столбцов типа Дата или Дата/Время также можно применять команду Вычесть дни (только в меню Дата на вкладке Добавление столбца). Или вычислить самые ранние / поздние даты.