Это фрагмент книги Гил Равив. 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 и формулу:
1 2 3 4 5 6 |
if [Country] = "US" then #date( [Release Date.3], [Release Date.1], [Release Date.2] ) else #date( [Release Date.3], [Release Date.2], [Release Date.1] ) |
Рис. 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. Меню Дата
Возраст – вычитает текущее дату/время из значения в столбце. По умолчанию возвращает число дней. Далее можно представить разницу в годах, часах, секундах и других единицах измерения.
Только дата – извлекает дату из столбца с типом Дата/Время. Вместе с преобразованием Только время (меню Время справа от меню Дата) можно разделить столбец Дата/Время на два отдельных столбца Дата и Время. Это упрощает модель, делает работу с ней гибче, уменьшает размер отчета и сокращает объем требуемой для него памяти.
Другие опции позволяют извлекать дату начала и конца периода, число дней в периоде и многое другое. При выборе нескольких столбцов типа Дата или Дата/Время также можно применять команду Вычесть дни (только в меню Дата на вкладке Добавление столбца). Или вычислить самые ранние / поздние даты.