Глава 11. Определение типов данных в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Power Query автоматически устанавливает типы данных. Причем, от версии к версии делает это всё лучше и лучше. Тем не менее, встречаются ситуации, когда его выбор покажется вам странным. А иногда Power Query и вовсе не может распознать тип данных.

Ris. 11.1. Ishodnaya tablitsa v Excel

Рис. 11.1. Исходная Таблица в Excel

Скачать заметку в формате Word или pdf, примеры в формате архива

Power Query распознает несколько типов данных (больше, чем Excel):

  • Число: десятичное, целое, валюта
  • Дата и время: дата/время, дата, время, дата/время/часовой пояс, продолжительность
  • Текст: текст
  • Логическое значение: ИСТИНА/ЛОЖЬ
  • Объект: бинарный (двоичный)
  • Неопределенный: любой

Наибольшие неудобства доставляет тип данных Любой. Его Power Query использует, чтобы указать, что не знает, какой тип данных выбрать. Данные, определенные как Любой, могут принимать различные формы при загрузке или ссылке в будущем.

Почему важны типы данных?

Чтобы продемонстрировать важность типов данных, вы создадите запрос, и удалите типы данных, определенные Power Query по умолчанию. Откройте файл Defining Data Types.xlsx (рис. 11.1). Выберите любую ячейку в Таблице на листе May –> Данные –> Из таблицы/диапазона. В редакторе Power Query в области ПРИМЕНЕННЫЕ ШАГИ удалите шаг Измененный тип. В этот момент типы данных во всех столбцах не определены:

Ris. 11.2. V stolbtse Data tip dannyh Lyuboj

Рис. 11.2. В столбце Data тип данных Любой; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Интересно, что каждый столбец в этом наборе данных имеет один и тот же тип данных – Любой, но все они выглядят по-разному. Даты выглядят так, как будто они находятся в формате даты/времени, а числа выглядят так, как будто они являются числовым типом данных. Так в чем же дело? Выполните следующие действия, чтобы узнать:

  • Переименуйте запрос – May
  • Главная –> Закрыть и загрузить

На листе Excel появится Таблица:

Ris. 11.3. CHto sluchilos s etim stolbtsom Date

Рис. 11.3. Что случилось с этим столбцом Date?

Excel не распознал столбец Date как даты, и вывел порядковые номера дат. Хотя это легко исправить (форматируя столбец как даты), это указывает на более серьезную проблему.

Power Pivot и любой тип данных

Измените запрос и «приземлите» его и на лист, и в модель данных. Для этого в файле Excel в области Запросы и подключения щелкните правой кнопкой мыши запрос May –> Загрузить в… В открывшемся окне Импорт данных установите флажок Добавить в модель данных (оставив выбранным параметр Таблица, рис. 11.4). Нажмите Ok. Появится окно предупреждения об изменениях. Подтвердите.

Ris. 11.4. Dobavit dannye v model dannyh Power Pivot

Рис. 11.4. Добавить данные в модель данных Power Pivot

Данные будут помещены в модель данных, а Таблица на листе Excel изменит свой вид:

Ris. 11.5. Strannye izmeneniya Tablitsy na liste Excel pri dobavlenii dannyh v model dannyh

Рис. 11.5. Странные изменения Таблицы на листе Excel при добавлении данных в модель данных

Простое добавление соединения к модели данных Power Pivot изменяет поле обратно на дату. Или нет? Чтобы проверить, выберите любую ячейку в столбце A, перейдите на вкладку Главная, посмотрите на поле формат. Никаких изменений нет! Более того, вы можете применить любой формат к данным в столбце A, и не увидите никаких изменений. Данные обрабатываются как текст.

Если вы откроете Power Pivot, вы увидите, что данные выглядят как дата/время, но тип данных – Текст. Это не позволит вам создать в Power Pivot таблицу календаря!

Ris. 11.6. Dannye v Power Pivot kotorye vyglyadyat kak daty yavlyayutsya tekstovymi

Рис. 11.6. Данные в Power Pivot, которые выглядят как даты, являются текстовыми

Если же вы попытаетесь изменить тип данных внутри Power Pivot, появится сообщение об ошибке:

Ris. 11.7. Dannye importirovannye po zaprosu ne mogut byt izmeneny v Power Pivot

Рис. 11.7. Данные, импортированные по запросу, не могут быть изменены в Power Pivot

«Правильные» типы данных

Исправить эти проблемы довольно просто. Отредактируйте запрос May и установите релевантные типы данных:

Ris. 11.8. Ispravlenie tipa dannyh u istochnika v zaprose

Рис. 11.8. Исправление типа данных у источника – в запросе

Проверьте: теперь, и на листе Excel, и в модели Power Pivot типы данных обрабатываются корректно. Помните, что Power Pivot форматирует даты в формате Дата/Время, причем 00:00:00 составляет 0 минут после полуночи указанной даты.

Объединение запросов с различными типами данных

Одна из типичных задач – объединение двух таблиц. Но что происходит, когда столбцы в этих таблицах имеют разные типы данных?

Поскольку запрос May уже исправлен, можно создать новый запрос для июньских данных без типов данных и посмотреть, что произойдет при их объединении. Выберите Таблицу Jun, пройдите по меню Данные –> Из таблицы/диапазона. В редакторе Power Query удалите шаг Измененный тип. Переименуйте запрос Jun. Главная –> Закрыть и загрузить… –> Только создать подключение.

У вас есть два запроса. В области Запросы и подключения щелкните правой кнопкой мыши на запросе Jun –> Добавить. В окне Добавление выберите вторую таблицу – May. Нажмите Ok. Если вы проверите тип данных в столбце Date, то увидите, что он – Любой.

Закройте запрос без сохранения, и попробуйте в другом порядке. В области Запросы и подключения щелкните правой кнопкой мыши на запросе Nay –> Добавить. В окне Добавление выберите вторую таблицу – Jun. Нажмите Ok. Не помогло – в столбце Date, по-прежнему тип Любой.

Не имеет значения, в каком порядке вы добавляете запросы: если типы данных в соединяемых таблицах не совпадают, они будут преобразованы в тип данных Любой. Исправьте типы данных в запросе Jun по аналогии с May, и лишь затем объедините запросы.

Рекомендация

Хотя Power Query довольно неплохо справляется с присвоением типов данных, мы рекомендуем после каждого импорта просмотреть шаг Измененный тип и проверить типы данных.

 


Прокомментировать