Глава 2. Изменение настроек Power Query, действующих по умолчанию

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

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

Иногда файл имеет расширение, которое не соответствует его содержимому. Предположим, вы работаете с набором данных CSV, который выглядит так при просмотре в блокноте:

Рис. 2.1. CSV-файл, который имеет дополнительную строку заголовка

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

Начиная с строки 2 файл содержит список данных, отображаемых в формате, разделенном запятыми, со строкой заголовка и четырьмя столбцами данных. Проблема в том, что в верхней части есть дополнительная строка без запятых. В CSV-файлах это лишнее, так как большинство программ работают с количеством столбцов на основе первой строки. Для Power Query это было проблемой.[1]

Переопределение настроек по умолчанию

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

Чтобы удалить шаг, выделите его в окне Примененные шаги и кликните на крестик (рис. 2). Внимание! При удалении шага, находящегося в середине запроса, может появиться ошибка, если удаленный шаг влиял на последующие шаги. К сожалению, в этой области нет функции отмены.

Рис. 2.2. Удаление шага

Вы также можете изменить способ, которым Power Query выполняет шаги. Это возможно, если справа от имени шага есть шестеренка. Например, выделите первый шаг – Источник, и кликните на шестеренке (см. рис. 2.2). Откроется окно конфигурации:

Рис. 2.3. Окно конфигурации шага Импорт

Нужно сообщить Power Query, что CSV не является правильным форматом для этого документа, даже если файл имеет расширение CSV. Щелкните раскрывающийся список в области Открыть файл как и выберите Текстовый файл, нажмите Ok, закрывая окно конфигурации. Запрос импортирует все данные, правда не разделенные на столбцы:

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

Ну что ж, все данные в наличии. Осталось их обработать.

• На вкладке Главная кликните Удалить строки –> Удалить верхние строки –> 1
• На этой же вкладке кликните Разделить столбец –> По разделителю (рис. 2.5)
• На этой же вкладке кликните Использовать первую строку в качестве заголовков

Рис. 2.5. Разделить столбец по каждому разделителю запятая

Изменение порядка шагов

Порядок шагов можно изменять. Для этого станьте на шаг, который вы хотите переместить и кликните правой кнопкой мыши:

Рис. 2.6. Контекстное меню работы с шагами

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

Типы дат

Серьезная проблема для профессионалов Excel – это импорт дат. Как только данные помещаются в Excel, даты не являются проблемой. Независимо от того, в какой стране будет открыта книга, даты будут отображаться корректно. Пользователи смогут выбирать требуемый формат, но Excel всегда будет понимать, что это даты.

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

Примеры в этой и предыдущей главе были построены с использованием стандартных настроек для дат США (MM/DD/YYYY).

Прим. Багузина. Поскольку я создаю скриншоты на ПК с российскими региональными настройками, при импорте файлов почти половина строк выдала ошибки (рис. 2.7).

Рис. 2.7. Power Query, использующий российские региональные настройки, не воспринял 01.13.2008, как дату

Чтобы победить эту проблему следует явно указать Power Query формат импортируемых данных. Для этого откройте редактор Power Query, удалите шаг Измененный тип. Щелкните правой кнопкой мыши столбец TransDate, пройдите по меню Тип изменения –> Используя локаль…

Рис. 2.8. Преобразовать тип данных, используя локаль

В открывшемся окне Изменение типа по локали измените Тип данных на Дата, и установите Языковый стандарт – Английский (США). В этом окне указывается язык источника данных.

Рис. 2.9. Изменение типа по локали

Нажмите Ok. Power Query поймёт, что в источнике данных используются даты в формате США, и поместит их в запрос в формате локальных настроек. В нашем случае – российских.

Поскольку ранее вы отменили преобразование типов данных для всей таблицs, выделите столбцы Account и Dept, и установите для них Целое число. Наконец, выделите Amount, пройдите по меню Тип изменения –> Используя локаль… В окне Изменение типа по локали измените Тип данных на Валюта, и установите Языковый стандарт – Английский (США). Запрос теперь выглядит следующим образом и готов к загрузке на рабочий лист:

Рис. 2.10. Преобразование дат и валюты после того, как Power Query явно указали, что в исходных данных использовался языковый стандарт США (а не установки по умолчанию, т.е. российские)

[1] Книга была написаны с использованием Power Query версии 2.24, выпущенной в июле 2015 г. Я использую версию 2.61 для Excel 365 образца июля 2019 г. И сейчас Power Query отлично справляется с лишней строкой. Однако я оставил описание примера, чтобы показать общий подход. – Прим. Багузина.