Power Query: импортируйте и преобразуйте данные как профи

Если вы каждую неделю тратите часы на скачивание CSV-файлов, удаление пустых строк, форматирование дат и написание сложных вложенных формул только для того, чтобы подготовить данные к анализу, вы работаете больше, чем нужно. Добро пожаловать в Power Query — самый мощный инструмент для автоматизации работы с данными, встроенный прямо в Microsoft Excel.

Часто называемый «Скачать и преобразовать» (Get & Transform Data), Power Query позволяет подключаться почти к любому источнику данных, очищать и изменять структуру информации, а затем загружать ее в таблицу. И самое главное? Он записывает ваши шаги. Когда вы получите новые данные в следующий раз, вам не придется повторять всю ручную работу; достаточно просто нажать Обновить.

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

Что такое Power Query?

Power Query — это механизм подключения и подготовки данных. В мире управления базами данных этот процесс известен как ETL: извлечение, преобразование и загрузка (Extract, Transform, Load).

• Извлечение: получение данных из различных источников (книги Excel, CSV и текстовые файлы, веб-страницы, базы SQL или даже облачные сервисы).
• Преобразование: очистка и изменение структуры данных. Сюда входят фильтрация строк, изменение типов данных, разделение текста, объединение таблиц и создание вычисляемых столбцов.
• Загрузка: вывод обработанных данных напрямую в таблицу Excel, сводную таблицу (PivotTable) или модель данных Power Pivot.

Традиционно пользователи Excel полагались на комбинацию таких функций, как TRIM, PROPER, SUBSTITUTE и VLOOKUP, в сочетании с ручным копированием и вставкой для решения этих задач. Power Query заменяет этот утомительный рабочий процесс наглядным и удобным визуальным интерфейсом.

Почему вам стоит изучить Power Query

Если вы все еще сомневаетесь, стоит ли изучать новый инструмент Excel, вот почему освоение Power Query станет прорывом для вашей продуктивности:

• Программирование не требуется: хотя Power Query работает на языке формул «M», 95% преобразований данных можно выполнить, используя только кнопки на ленте и контекстные меню по клику правой кнопкой мыши.
• Автоматизация без VBA: вам не нужно знать, как писать макросы для автоматизации рутинных задач. Power Query запоминает ваши «Примененные шаги» и автоматически выполняет их для новых данных.
• Работа с большими данными: в Excel существует физическое ограничение в 1 048 576 строк. Power Query может подключаться к наборам данных с миллионами строк, агрегировать данные в фоновом режиме и загружать в вашу таблицу только сводные результаты.
• Неразрушающее редактирование: Power Query никогда не изменяет исходный источник данных. Он создает подключение, считывает данные и выдает очищенную копию.

Начало работы: редактор Power Query

Чтобы открыть Power Query, создайте пустую книгу Excel и перейдите на вкладку Данные на ленте. Найдите группу Скачать и преобразовать в левой части.

Здесь вы можете нажать Получить данные, чтобы увидеть выпадающее меню доступных источников данных. После выбора файла и нажатия «Преобразовать данные» Excel откроет Редактор Power Query в новом окне. Этот интерфейс состоит из четырех основных областей:

1. Лента: находится сверху и содержит вкладки, такие как «Главная», «Преобразование», «Добавление столбца» и «Просмотр». Здесь находятся все ваши инструменты.
2. Панель запросов: расположена слева и отображает список всех активных подключений к данным в вашей книге.
3. Предварительный просмотр данных: центральная сетка, показывающая, как выглядят ваши данные на текущем выбранном шаге.
4. Панель параметров запроса (Примененные шаги): находится справа. Это самая важная часть редактора. Каждый раз, когда вы что-то меняете, Power Query записывает это здесь в виде последовательного шага. Вы можете в любое время удалить, изменить порядок или отредактировать эти шаги.

Пошаговое руководство: очистка беспорядочных данных о продажах

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

Вот образец наших сырых, грязных данных:

Системный экспорт: Отчет о продажах за 3 квартал	Столбец2	Столбец3
Сформировано: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

Если бы мы использовали традиционные формулы, нам пришлось бы применить LEFT, RIGHT, FIND и VALUE для извлечения имен торговых представителей и исправления чисел. Вместо этого давайте воспользуемся Power Query.

Шаг 1: Импорт данных

Сохраните данные в виде файла CSV или Excel. Откройте новую книгу Excel, перейдите в Данные > Получить данные > Из файла и выберите ваш файл. Когда появится окно предварительного просмотра, нажмите Преобразовать данные. Откроется редактор Power Query.

Шаг 2: Удаление верхних строк и использование первой строки в качестве заголовков

Первые две строки наших данных — это метаданные системного экспорта, а не фактические записи данных. Нам нужно от них избавиться.

• На вкладке «Главная» нажмите Удалить строки > Удалить верхние строки. Введите «2» и нажмите ОК.
• Теперь наши настоящие заголовки столбцов находятся в строке 1. Нажмите кнопку Использовать первую строку в качестве заголовков на вкладке «Главная». Моментально "Rep_ID_Name", "Order_Date" и "Revenue" станут вашими официальными названиями столбцов.

Шаг 3: Разделение столбца представителя

Столбец "Rep_ID_Name" содержит как идентификационный номер, так и имя сотрудника, разделенные дефисом.

• Щелкните правой кнопкой мыши по заголовку столбца "Rep_ID_Name".
• Выберите Разделить столбец > По разделителю.
• Выберите «Пользовательский» и введите дефис ( - ). Нажмите ОК.
• Power Query разделит данные на два столбца. Теперь вы можете дважды щелкнуть по новым заголовкам столбцов, чтобы переименовать их в "Rep_ID" и "Rep_Name".

Чтобы убрать символы подчеркивания в имени Боба (Bob_Jones), щелкните правой кнопкой мыши по столбцу Rep_Name, выберите Замена значений, введите символ подчеркивания (_) в поле «Значение для поиска» и оставьте поле «Заменить на» пустым или добавьте пробел. Нажмите ОК.

Шаг 4: Стандартизация типов данных

Заметили, что наши даты и выручка представлены в совершенно разных форматах? Power Query легко приводит их к единому стандарту.

• Нажмите на маленькую иконку в левой части заголовка столбца "Order_Date" (вероятно, там написано «ABC» или «Любой»). Измените его на Дата. Power Query автоматически преобразует различные форматы дат в единый стандартный вид.
• Нажмите на иконку рядом с "Revenue" и измените тип на Валюта (или «Десятичное число»). Это уберет знаки доллара и аккуратно отформатирует все значения.

Шаг 5: Добавление условного столбца

Допустим, мы хотим классифицировать продажи свыше 1000 долларов как "High Value" (Высокая ценность). Вместо того чтобы писать сложную функцию IF вроде =IF(C2>=1000, "High Value", "Standard") в Excel, мы можем использовать интерфейс Power Query.

Перейдите на вкладку Добавление столбца и нажмите Условный столбец. Установите правила: Если [Revenue] больше или равно 1000, вывести "High Value", иначе "Standard". В фоновом режиме Power Query сгенерирует следующий код M для этого шага:

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

Слияние запросов: современная альтернатива VLOOKUP

Одной из самых распространенных задач в анализе данных является объединение таблиц. Если у вас есть отдельная таблица с регионами для каждого торгового представителя, вы, как правило, обращаетесь к нашему полному руководству по VLOOKUP, чтобы подтянуть эти данные.

Однако выполнение тысяч формул VLOOKUP или связок INDEX и MATCH может значительно замедлить работу вашей книги. В Power Query для этого используется функция Объединить запросы.

Просто импортируйте обе таблицы в Power Query, выберите вашу основную таблицу продаж и нажмите Объединить запросы на вкладке «Главная». Выберите вторую таблицу (таблицу регионов), щелкните по совпадающему столбцу в обеих таблицах (например, "Rep_ID") и нажмите ОК. Power Query выполнит аналог сверхбыстрого VLOOKUP за секунды, независимо от того, десять у вас строк или десять миллионов.

Отмена свертывания (Unpivot): секретное оружие Power Query

Часто вы получаете данные, которые уже сгруппированы в сводную структуру (например, месяцы расположены по столбцам: Янв, Фев, Мар, Апр). Хотя человеку читать это удобно, для создания диаграмм или сводных таблиц такой формат ужасен.

Выберите столбцы-идентификаторы (например, Rep Name), щелкните правой кнопкой мыши по заголовку и выберите Отменить свертывание других столбцов. Power Query мгновенно преобразует ваши широкие кросс-табличные данные в плоский табличный макет с новыми столбцами «Атрибут» (Месяц) и «Значение» (Продажи). Сделать это с помощью стандартных формул Excel практически невозможно, что делает функцию отмены свертывания одной из самых популярных в Power Query.

Загрузка преобразованных данных

Когда ваши данные идеально очищены, самое время отправить их обратно в Excel.

На вкладке «Главная» нажмите Закрыть и загрузить. По умолчанию ваши преобразованные данные будут загружены в абсолютно новую зеленую таблицу Excel на новом листе. Если вы предпочитаете отправить данные сразу на этап анализа, вы можете нажать стрелку выпадающего списка, выбрать Закрыть и загрузить в... и вместо этого указать «Отчет сводной таблицы». Если вам нужно освежить знания по созданию таких отчетов, загляните в наше руководство: создание сводных таблиц для начинающих.

Магия кнопки «Обновить»

Истинная мощь Power Query становится очевидной на следующей неделе, когда вы получаете новый необработанный экспорт продаж. Не повторяйте описанные выше шаги!

Просто сохраните новый CSV-файл поверх старого (оставив точно такое же имя файла и расположение в папке). Затем откройте книгу Excel, щелкните правой кнопкой мыши в любом месте вашей таблицы с чистыми данными и нажмите Обновить.

Power Query обратится к файлу, заново применит каждый шаг — удаление строк, поднятие заголовков, разделение столбцов, замену текста, проверку условий и слияние таблиц — и обновит ваш финальный результат за доли секунды. Это важнейший компонент рабочих процессов по автоматизации Excel.

Упрощение работы с данными с помощью ИИ

Хотя Power Query блестяще справляется со структурными преобразованиями, иногда вам нужна специфическая условная логика или сложный синтаксический анализ текста, требующие продвинутых формул Excel или пользовательского кода M. Вместо того чтобы искать ответы на форумах, вы можете воспользоваться искусственным интеллектом.

Если вам сложно написать идеальное вычисление для пользовательского столбца, GPTExcel станет отличным помощником. Просто опишите, чего вы хотите достичь, простыми словами — например, «мне нужна формула, чтобы извлечь только числа из смешанной текстовой строки» — и GPTExcel мгновенно сгенерирует правильную формулу или код M. Сочетание Power Query с ИИ для очистки данных дает вам непревзойденный набор инструментов для аналитики.

Часто задаваемые вопросы

Изменяет ли Power Query мой исходный файл данных?

Нет. Power Query создает одностороннее подключение к исходным данным. Он считывает данные, применяет преобразования в памяти и выводит новый результат в Excel. Ваш исходный CSV-файл, база данных или книга остаются абсолютно нетронутыми и в безопасности.

Могу ли я использовать Power Query на Mac?

Да, Microsoft значительно улучшила поддержку Power Query в Excel для Mac. И хотя в версии для Mac исторически отсутствовали некоторые продвинутые коннекторы и элементы интерфейса, доступные в Windows, теперь вы можете легко подключаться к локальным файлам, базам данных и обновлять существующие запросы в современных версиях Microsoft 365.

В чем разница между слиянием (Merge) и добавлением (Append) в Power Query?

Слияние (Merge) — это аналог VLOOKUP или INDEX/MATCH. Вы используете его для добавления новых столбцов данных путем сопоставления общего идентификатора между двумя таблицами. Добавление (Append) похоже на копирование и вставку данных в конец листа. Вы используете его, чтобы ставить таблицы друг под другом, добавляя новые строки (например, объединяя продажи за январь и продажи за февраль).

Почему обновление Power Query завершилось с ошибкой?

Самая распространенная причина сбоя при обновлении запроса — исходный файл был перемещен, переименован или удален. Другая частая проблема — изменение заголовка столбца в исходных данных (например, система поменяла "Revenue" на "Total Revenue"). Вы можете исправить это, открыв редактор Power Query, перейдя на панель «Примененные шаги» и обновив шаг «Источник» (Source) или переименовав столбец в логике вашего шага.