Power Query в Excel: автоматизация очистки данных

Какую задачу решает Power Query

Power Query — встроенный в Excel механизм импорта и преобразования данных (Данные → Получить данные). Вместо того чтобы каждую неделю вручную чистить выгрузку, вы один раз описываете шаги. Excel сохраняет их как запрос и повторяет при каждом обновлении.

Главные идеи:

• Исходный файл не меняется. Power Query читает его и загружает очищенную копию.
• Каждое действие становится шагом в списке Примененные шаги. Шаги можно переименовать, переставить или удалить.
• За шагами стоит язык формул M. Работать можно и без него, но умение его читать помогает искать ошибки.

Инструмент подходит для повторяющихся задач: еженедельные выгрузки продаж, банковские выписки, данные из CRM, отчёты филиалов в одинаковом формате.

Импорт из файлов и папок

Один файл: Данные → Получить данные → Из файла → Из книги, Из текстового/CSV-файла и так далее. Выберите лист или таблицу и нажмите Преобразовать данные, а не «Загрузить», чтобы открыть редактор Power Query.

Целая папка — самый полезный сценарий. Сложите все ежемесячные файлы одинаковой структуры в одну папку, затем:

1. Данные → Получить данные → Из файла → Из папки, выберите папку.
2. Нажмите Объединить и преобразовать данные и укажите нужный лист или таблицу.
3. Power Query создаст вспомогательный запрос на основе файла примера. Шаги очистки, которые должны применяться к каждому файлу, добавляйте в «Преобразовать файл примера», а шаги над общим результатом — в основной запрос.

Дальше достаточно положить новый файл в папку и обновить отчёт.

Совет: сразу после шага источника отфильтруйте список файлов по расширению, чтобы не подхватились временные файлы (например, начинающиеся с ~$) и случайные документы.

Очистка и преобразование

Самые частые шаги, все доступны на ленте:

• Использовать первую строку в качестве заголовков и Удалить первые строки для выгрузок с шапкой над таблицей.
• Тип данных: даты, числа, текст. Задавайте типы осознанно и указывайте языковой стандарт («С использованием локали»), если даты или дробные числа пришли в другом региональном формате.
• Обрезать и Очистить убирают лишние пробелы и непечатаемые символы — частую причину, почему не работает поиск совпадений.
• Замена значений, Заполнить вниз для пустых ячеек под заголовком группы, Удалить дубликаты.
• Разделить столбец по разделителю, например «Фамилия, Имя».
• Отменить свертывание столбцов, чтобы месяцы, разложенные по столбцам, стали строками. Сводные таблицы работают с таким видом гораздо лучше.
• Фильтр итоговых строк, пустых строк и тестовых записей.

Так подобные шаги выглядят в M (Главная → Расширенный редактор):

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Trimmed = Table.TransformColumns(Source, {{"Client", Text.Trim, type text}}),
    Typed = Table.TransformColumnTypes(Trimmed, {{"Date", type date}, {"Amount", type number}}),
    NoBlanks = Table.SelectRows(Typed, each [Amount] <> null)
in
    NoBlanks

Слияние и добавление таблиц

Две разные операции, которые часто путают:

При объединении вы выбираете ключевые столбцы и тип соединения: внешнее слева (все строки первой таблицы), внутреннее (только совпадения), анти-соединение слева (строки без пары, удобно для поиска пробелов) и другие. После объединения разверните новый столбец и отметьте только нужные поля.

Перед объединением убедитесь, что ключи одного типа и без лишних пробелов, иначе «123» и «123 » не совпадут.

Обновление отчёта в один клик

Через Закрыть и загрузить в… выберите, куда пойдёт результат: таблица на листе, сводная таблица или Только создать подключение для промежуточных запросов, которые не должны засорять книгу.

Затем:

• Данные → Обновить все перезапускает все запросы и обновляет построенные на них сводные таблицы.
• В свойствах запроса можно включить обновление при открытии файла или обновление по интервалу.
• Храните путь к папке в параметре (Управление параметрами), тогда отчёт можно перенести на другой компьютер, изменив одно значение.

Частые ошибки

• Жёстко прописанные имена столбцов. Если источник переименует столбец, шаги вроде смены типа упадут. Договоритесь о стабильном формате выгрузки или переименовывайте столбцы в одном раннем шаге.
• Загрузка всего на листы. Вспомогательные запросы должны быть только подключениями.
• Разнородные файлы в папке. Разная структура ломает объединение.
• Ошибки уровней конфиденциальности при объединении источников. Проверьте Параметры запроса → Конфиденциальность, а не отключайте защиту вслепую.