- Power Query позволяет объединять несколько файлов из локальной или облачной папки при условии, что они используют одну и ту же схему данных.
- Использование образца файла упрощает применение пакетных преобразований, таких как удаление верхних строк или изменение заголовков.
- При неточном совпадении столбцов возникают ограничения, требующие ручной корректировки или использования скриптов VBA в сложных случаях.
Представьте, что у вас есть множество ежемесячных или еженедельных отчетов, разбросанных по папкам, и вместо того, чтобы тратить утро на лихорадочное копирование и вставку данных, вы могли бы объединить все в одну электронную таблицу Excel всего за пару кликов. Именно здесь на помощь приходит Power Query — инструмент, который, по сути, является незаменимым помощником для любого, кто работает с данными, позволяя без труда объединять файлы, имеющие одинаковую структуру.
Самое замечательное в этой системе то, что она не ограничивает вас локальной папкой на вашем ПК; вы можете получать данные с сайтов SharePoint, Azure Data Lake или Azure BLOB-объектов . Ключ к бесперебойной работе заключается в том, что файлы имеют одинаковую схему, то есть столбцы совпадают, хотя Power Query достаточно интеллектуален, чтобы помочь очистить данные, если они содержат лишний шум или ненужные строки.
Пошаговый процесс комбинирования
При подключении к источнику данных первое, что вы увидите, — это представление файловой системы. Здесь наиболее важным столбцом является «Содержание», который содержит двоичное представление каждого документа. Если вы щелкнете по одному из этих двоичных значений, программа попытается определить, является ли это файл CSV, JSON или Excel, чтобы открыть его. Для объединения всего пакета у вас есть два варианта: нажать кнопку «Объединить» напрямую или, если вы более внимательны, выбрать «Преобразовать данные», чтобы отфильтровать список файлов (например, чтобы игнорировать определенные папки) перед продолжением объединения.
После открытия диалогового окна слияния Power Query использует образец файла для анализа структуры . По умолчанию выбирается первый файл в списке, но вы можете изменить его, если есть другой файл, который лучше представляет набор данных. Здесь вы можете выбрать параметр пропуска файлов с ошибками , что очень полезно, когда отчет поврежден или пуст, предотвращая остановку всего процесса.
Управление выводом и очистка данных
После принятия слияния система выполняет ряд технических манипуляций: она создает пример запроса и параметризованную функцию. Это означает, что любые изменения, внесенные в пример файла, будут автоматически реплицированы во все остальные файлы в папке. Например, если в ваших CSV-файлах есть четыре строки ненужных заголовков перед фактическими данными, вы можете перейти к запросу преобразования и использовать опцию удаления верхних строк , чтобы очистить файл от лишнего.
После очистки строк необходимо применить функцию « Использовать первую строку в качестве заголовков» . Имейте в виду, что при этом Power Query обычно добавляет шаг автоматического преобразования типов данных. Если при возвращении к основному запросу вы увидите ошибку, указывающую на то, что столбец «Column1» не найден, не беспокойтесь; это нормально, поскольку вы переименовали столбцы в примере , и последующий шаг больше не распознает старые имена. Просто удалите этот ошибочный шаг из панели «Примененные шаги», и все вернется в норму.
Общие проблемы и передовые альтернативы
Иногда жизнь бывает непростой, и мы сталкиваемся с файлами, которые не идентичны. Например, если в отчетах ServiceNow один заголовок называется «resolved_on», а другой — «closed_on», Power Query может создать проблемы, поскольку требует согласованной структуры . Если образец файла не совсем совпадает с остальными, инструмент может пропустить информацию из несовпадающих файлов или выдать ошибки импорта.
В случаях, когда структура данных совершенно хаотична или объем данных огромен (превышает миллион строк в Excel), существуют другие варианты. Некоторые пользователи выбирают скрипты VBA с использованием CSV-интерфейсов , которые позволяют объединять файлы без повторения заголовков, независимо от того, меняется ли разделитель полей или записей от файла к файлу. Хотя оболочка Windows работает очень быстро, она часто дает сбои при обработке встроенных переносов строк, в то время как надежное решение на основе VBA не перегружает оперативную память и может легко обрабатывать миллионы записей.
Для завершения процесса после объединения данных в Power Query не забудьте присвоить каждому столбцу правильный тип данных (Дата для дат, Валюта для доходов и т. д.) перед загрузкой итоговой таблицы. Вы можете проверить правильность всех данных, отфильтровав столбец «Имя источника», чтобы убедиться, что все файлы в папке были успешно обработаны.
Возможность автоматизировать объединение нескольких CSV-файлов позволяет перейти от утомительной ручной работы к профессиональному рабочему процессу, будь то использование гибкости Power Query для очистки данных или обращение к VBA в случаях несовместимости структур или превышения объемов данных в электронных таблицах.