Power Query в Excel: импорт, очистка, объединение и обновление данных
By Mirko Peters · · Updated
Power Query в Excel импортирует и очищает данные без формул. Расскажем, где его найти, как работают From Table/Range и Applied Steps, в чём разница между merge и append, как загрузить результат и обновить данные.
Automatically translated with AI from the original post. Read the English original
Power Query в Excel — это инструмент на вкладке Данные, в группе Получение и преобразование данных, который подключается к данным, очищает и перестраивает их и загружает результат на лист. Шаги, которые вы выполняете, записываются и повторяются при каждом обновлении, поэтому ежемесячная очистка сводится к одному щелчку. Статья адресована пользователям Excel, которые каждую неделю копируют, вставляют и исправляют одни и те же выгрузки и хотят от этого избавиться. Вы узнаете, с чего начать, как очистить таблицу, как выполнять append и merge, как загружать и обновлять данные, а также где проходят ограничения на Mac и в веб-версии.
Ключевые выводы
- Power Query в Excel находится на вкладке «Данные» в группе «Получение и преобразование данных». Он подключается к данным, преобразует их и загружает на лист или в модель данных.
- Каждое изменение записывается как шаг в Applied Steps. Исходный источник не меняется, а шаги повторно выполняются при каждом обновлении.
- Append добавляет строки из таблиц с одинаковыми столбцами. Merge объединяет две таблицы по совпадающим ключевым столбцам, при этом совпадающие столбцы должны иметь одинаковый тип данных.
- Используйте «Данные» > «Обновить все», чтобы заново выполнить запросы. В «Свойствах подключения» можно настроить обновление с заданным интервалом или при открытии файла.
- Excel для Mac и Excel для веба поддерживают часть возможностей Power Query, при этом документация Microsoft по тому, что именно можно создавать в каждой версии, отличается, поэтому проверьте возможности своей сборки.
Microsoft описывает четыре этапа: подключение к данным, их преобразование, объединение с другими источниками и загрузка. Исходный источник на всех этапах остаётся без изменений. Именно это делает эксперименты безопасными: исходную выгрузку невозможно испортить.
Что такое Power Query и где его найти
Power Query — это механизм подготовки данных с графическим редактором. Согласно странице Microsoft о том, что такое Power Query, он выполняет извлечение, преобразование и загрузку данных, не имеет собственного хранилища и оставляет выбор места назначения за приложением-хостом. Excel — один из хостов. Power BI Desktop — другой, и оба используют общий редактор. Коннекторы и места назначения могут различаться в зависимости от хоста, поэтому не стоит считать, что запрос, который вы видели в одном продукте, будет выглядеть так же в другом.
Где находится Power Query, зависит от версии Excel — согласно обзору Power Query в Excel от Microsoft:
- Excel 2016, 2019 и Microsoft 365 для Windows. Встроен. Откройте вкладку Данные и найдите группу Получение и преобразование данных.
- Excel 2010 и 2013 для Windows. Power Query был бесплатной надстройкой со своей вкладкой на ленте. Microsoft объявила его устаревшим, хотя он может по-прежнему работать.
- Excel для Mac. Поддерживает обновление данных из нескольких источников. В Excel 2016 и 2019 для Mac он недоступен.
- Excel для веба. Поддерживается частично. Подробнее ниже.
В Windows Microsoft также указывает в требованиях .NET Framework 4.7.2 или более позднюю версию и WebView2 Runtime для веб-коннектора. Если команды отсутствуют на рабочем компьютере, сначала уточните у ИT-отдела, актуальна ли сборка, прежде чем считать, что функция удалена.
Как использовать Power Query в Excel: первый запрос
Проще всего начать с данных, которые уже есть в книге.
- Щелкните любую ячейку в таблице Excel. Если данные оформлены как обычный диапазон, сначала нажмите Ctrl+T, чтобы преобразовать их в таблицу.
- Выберите Данные > Получить и преобразовать данные > Из таблицы/диапазона.
- Откроется Редактор Power Query с предварительным просмотром данных.
- Внесите нужные изменения (см. следующий раздел).
- Выберите Главная > Закрыть и загрузить.
На странице Microsoft об импорте из таблицы Excel сказано, что по умолчанию результат выводится на новый лист, а исходный лист не меняется. Если нужно другое место назначения, используйте Загрузить в. Переименуйте новый лист, чтобы его не путали с источником.
Чтобы подключиться к другому источнику, используйте Данные > Получить данные и выберите источник: файл, базу данных, онлайн-сервис или веб. После выбора вы увидите предварительный просмотр и сможете выбрать Загрузить (сразу в Excel) или Преобразовать данные (сначала открыть редактор). Мой совет: выбирать Преобразовать данные почти всегда, даже если данные кажутся чистыми. В редакторе задаются типы столбцов, а неверные типы вызывают большинство ошибок обновления позже.
Если нужно начать с пустого запроса, на той же странице указаны пути Данные > Получить данные > Из других источников > Пустой запрос, а также Запустить редактор Power Query в меню «Получить данные».
Редактор простыми словами
- Область предварительного просмотра данных показывает таблицу и обновляется после каждого изменения.
- Область Параметры запроса содержит список ПРИМЕНЕННЫХ ШАГОВ. Каждое ваше действие добавляет шаг, например Источник или Измененный тип. Можно выбрать шаг, чтобы увидеть данные на этом этапе, изменить шаг или удалить его.
- Область Запросы показывает все запросы в книге. Откройте ее стрелкой рядом с предварительным просмотром.
- Расширенный редактор позволяет просматривать и редактировать скрипт на языке Power Query M. Microsoft отмечает, что каждый шаг, созданный в интерфейсе, записывается на языке M. Чтобы получить пользу от инструмента, трогать его не нужно.
Очистка и преобразование данных с помощью шагов
Типичные задачи по очистке в редакторе: фильтрация строк, удаление столбцов, изменение типов данных, разделение столбца, замена значений. Редактор записывает каждое действие как шаг. Команды находятся на ленте редактора и в меню, которое появляется при щелчке правой кнопкой мыши по заголовку столбца. Какие команды доступны, зависит от типа столбца, поэтому сначала проверьте меню заголовка, а потом ищите нужное на ленте.
Порядок действий, который подходит для большинства выгрузок:
- Сначала задайте типы. Проверьте значок типа рядом с заголовком каждого столбца. Даты, числа и текст ведут себя по-разному на последующих шагах и при объединении.
- Удалите лишнее. Меньше столбцов означает меньший и более быстрый результат и меньше сюрпризов при изменении источника.
- Отфильтруйте строки. Используйте стрелку в заголовке столбца, чтобы убрать пустые значения или ненужные значения.
- Исправьте текст. Замените опечатки и старые коды, уберите лишние пробелы или разделите столбец, в котором содержатся две части информации.
- Переименуйте запрос в области «Параметры запроса», чтобы потом его легко найти.
Поскольку «Примененные шаги» представлены списком, можно исправить ошибку на более раннем шаге без повторной настройки всего запроса. Если шаг выдает ошибку после изменения источника, посмотрите на первый шаг с ошибкой. Чаще всего он указывает на столбец, имя или тип которого изменились.
Одно предостережение о переименованных столбцах: на странице Microsoft об обновлении сказано, что новые столбцы источника появляются в Power Query только после проверки шага Источник, а желтая полоска с сообщением, что предварительный просмотр может быть устаревшим, означает необходимость обновить локальный кэш. Если новый столбец не появился, сначала проверьте эти два момента.
Объединение данных: добавление и слияние
Эти две команды выполняют разные задачи, и их часто путают.
Append объединяет строки. Используйте его, когда у вас много таблиц одинаковой структуры, например по одной выгрузке за каждый месяц. Microsoft описывает append как добавление строк одного запроса к другому. Существует два варианта: inline append добавляет данные к существующему запросу, а intermediate append каждый раз создает новый запрос.
Merge соединяет две таблицы по горизонтали на основе совпадающих значений, как поиск по справочнику. На странице Microsoft about merging queries описаны шаги:
- Убедитесь, что у вас есть как минимум два запроса со столбцом для сопоставления. Сопоставляемые столбцы должны иметь одинаковый тип данных, например «Текст» или «Число».
- В редакторе откройте Главная > Объединить запросы. Результат становится новым шагом в текущем запросе. Если нужен отдельный запрос, откройте стрелку рядом с «Объединить запросы» и выберите Объединить запросы как новые.
- Выберите основную таблицу и ее ключевой столбец, затем связанную таблицу и ее ключевой столбец. Если ключевых столбцов несколько, выберите их в одном и том же порядке в обеих таблицах.
- Проверьте количество совпадений, которое показано в диалоговом окне. Если оно меньше ожидаемого, скорее всего, ключевые столбцы отличаются написанием, пробелами или типом.
- Выберите Тип соединения (см. ниже) и нажмите ОК.
- Появится новый столбец. Нажмите значок Развернуть рядом с его заголовком и отметьте связанные столбцы, которые хотите добавить.
Типы соединений по данным Microsoft:
- Left outer сохраняет все строки основной таблицы и совпадения из связанной таблицы.
- Right outer сохраняет все строки связанной таблицы и совпадения из основной таблицы.
- Full outer сохраняет все строки из обеих таблиц.
- Inner сохраняет только строки, которые совпадают в обеих таблицах.
- Left anti сохраняет строки основной таблицы, для которых нет совпадений. Right anti сохраняет строки связанной таблицы, для которых нет совпадений.
- Cross создает пары из каждой строки основной таблицы с каждой строкой связанной таблицы.
Порядок таблиц важен, потому что left и right зависят от того, какую таблицу вы выбрали первой. Флажок Использовать нечеткое сопоставление для объединения сопоставляет приблизительные значения, и, по словам Microsoft, работает только с текстовыми столбцами. Если раньше вы использовали поиск, ближе всего к нему объединение с left outer, а left anti удобен для быстрой проверки строк без совпадений, например заказов без записи о клиенте.
Microsoft также упоминает уровни конфиденциальности. Power Query относит каждый источник к категории Public, Organizational или Private и использует эти категории, чтобы предотвратить случайное объединение данных так, что частная информация может стать доступной другому источнику. Если при объединении появляется запрос об уровнях конфиденциальности, внимательно прочитайте сообщение, прежде чем продолжить.
Загрузка запроса и его обновление
Закройте редактор командой Главная > Закрыть и загрузить. На странице Microsoft о создании, загрузке и редактировании запроса описаны следующие назначения:
- Закрыть и загрузить отправляет результат на лист.
- Закрыть и загрузить в позволяет выбрать другое назначение, например модель данных. В диалоговом окне «Импорт данных» установите флажок Добавить эти данные в модель данных, если планируете создавать сводные таблицы по нескольким таблицам.
- Если «Загрузить в» недоступно, используйте «Закрыть и загрузить», затем перейдите в Данные > Запросы и подключения, щелкните запрос правой кнопкой мыши на вкладке «Запросы» и выберите Загрузить в.
Чтобы изменить параметры по умолчанию, перейдите в Файл > Параметры > Параметры запросов > Загрузка данных. Можно использовать стандартные параметры загрузки или задать собственные значения по умолчанию для загрузки на лист или в модель данных.
Чтобы изменить запрос позже, выберите ячейку в его данных и выберите Запрос > Изменить, либо щелкните запрос правой кнопкой мыши в разделе Запросы и подключения и выберите Изменить.
Обновление
При обновлении все шаги выполняются заново для текущего источника. Способы обновления описаны на странице Microsoft refresh page:
- Данные > Обновить все обновляет всё содержимое книги (Ctrl+Alt+F5). Стрелка рядом предлагает команду Обновить для выбранного элемента (Alt+F5). Клавиша Esc останавливает обновление.
- Чтобы настроить автоматическое обновление, перейдите в Данные > Запросы и подключения, откройте вкладку Подключения, щёлкните подключение правой кнопкой мыши и выберите Свойства. На вкладке Использование можно задать Обновлять каждые (в минутах) и Обновлять данные при открытии файла.
- Параметр Включить фоновое обновление на той же вкладке позволяет продолжать работу, пока выполняется запрос. Он недоступен для запросов OLAP и подключений, которые питают модель данных.
Сохранённые пароли не шифруются, и Microsoft не рекомендует их сохранять. Снимите флажок Сохранить пароль на вкладке Definition, и Excel будет запрашивать пароль один раз за сеанс.
Если вы отправляете книгу другому пользователю, ему может понадобиться доступ к источнику. Запрос, который читает файл на вашем диске или сайт, недоступный получателю, на его компьютере завершится ошибкой. Разместите источник данных в общем расположении или отправьте загруженные результаты как значения.
Power Query на Mac и в Excel для Интернета
Обе платформы поддерживают часть этого инструмента, однако страницы Microsoft описывают их возможности не полностью совпадающим образом.
Excel для Интернета. На странице Microsoft сказано, что можно импортировать данные из нескольких источников: книги Excel, Текст/CSV, XML, JSON, SQL Server, списка SharePoint Online и OData, а также создать пустую таблицу или пустой запрос. Открыть Редактор Power Query можно через Данные > Получить данные > Запустить редактор Power Query, загрузить результат через Закрыть и загрузить, а обновить через Данные > Обновить все. Просматривать и обновлять запросы могут все подписчики Microsoft 365, а для расширенных возможностей нужен план для бизнеса или корпоративный план (E3 и выше). Некоторые функции требуют включения сторонних файлов cookie в браузере.
Excel для Mac. На одной из страниц Microsoft сказано, что начать нужно с Данные > Получить данные > Получить данные (Power Query), что для этого требуется Microsoft 365 и версия 16.69 или новее, а команда Преобразовать данные открывает редактор. На той же странице отдельный раздел утверждает, что создание запросов в редакторе недоступно в Excel для Mac, и описывает написание запросов на VBA в Windows с последующим импортом модуля на Mac. Эти два утверждения я согласовать не могу, поэтому перед тем как строить на этом процесс, проверьте всё на собственном Mac. Импорт из SQL Server описан как доступный только в бета-версии для участников Insiders.
Когда Power Query недостаточно
Скажу прямо: Power Query предназначен для подготовки данных, а не для их хранения. Microsoft указывает, что место вывода результатов определяет программа-хост, а в Excel это лист или модель данных. Лист имеет фиксированное ограничение по количеству строк, поэтому очень большой результат запроса на лист не поместится. В таком случае загрузите его в модель данных или перенесите обработку в базу данных либо в специализированный конвейер.
Стоит знать и другие ограничения:
- Запросы читают источник в момент выполнения. Они не передают изменения из источника в книгу в режиме реального времени.
- Совместная работа затруднена. Редактировать один и тот же запрос в общей книге двум людям одновременно — плохая идея. Договоритесь, кто владеет запросом, и давайте запросам понятные имена.
- Если вы работаете с регулируемыми данными, помните, что книгу легко скопировать. Следуйте правилам вашей организации о том, где такие файлы можно хранить.
Распространённые ошибки
- Пропуск типов данных и последующее удивление, почему слияние ничего не находит.
- Слияние по столбцам разных типов или с конечными пробелами.
- Использование слияния там, где нужно было добавление, и наоборот.
- Загрузка всего на лист, когда лучше подходит запрос только для подключения или модель данных.
- Сохранение пароля источника в подключении.
- Забытое обновление перед отправкой отчёта.
- Перемещение исходного файла без понимания, почему запрос начинает выдавать ошибку.
Смежные темы для изучения
- Удаление дубликатов в редакторе. Power Query может удалить дубликаты в таблице как отдельный шаг, который можно повторить, в отличие от разовой команды на ленте.
- Отмена сводки столбцов. Преобразуйте столбцы месяцев в строки перед созданием сводной таблицы. Microsoft упоминает Power Query именно для этой задачи.
- Параметры. У Microsoft есть страница о параметризованных запросах, которые позволяют повторно использовать запрос с разными входными данными.
- Язык M. Узнайте, как читать скрипт в расширенном редакторе, начиная со страницы Power Query M.
- Power BI. Узнайте, как тот же редактор работает в Power BI Desktop, когда ваших данных становится больше, чем может вместить Excel.
Следующий шаг
Выберите один экспорт, который вы каждую неделю чистите вручную. Преобразуйте его в таблицу, пропустите через «Из таблицы/диапазона», задайте типы столбцов и загрузите на новый лист. На следующей неделе вставьте новый экспорт в таблицу и нажмите «Обновить все». Вот так Power Query в Excel и делает свою работу.
Источники
- О Power Query в Excel (поддержка Microsoft)
- Что такое Power Query? (Microsoft Learn)
- Импорт из таблицы Excel (поддержка Microsoft)
- Создание, загрузка и изменение запроса в Excel (Power Query) (поддержка Microsoft)
- Добавление запросов (Power Query) (поддержка Microsoft)
- Объединение запросов (Power Query) (поддержка Microsoft)
- Обновление внешнего подключения к данным в Excel (поддержка Microsoft)
- Использование Power Query в Excel для Интернета (поддержка Microsoft)
- Импорт и формирование данных в Excel для Mac (Power Query) (поддержка Microsoft)