Как объединить несколько файлов excel в один power query

Как объединить файлы с использованием PowerQuery?

Объединить несколько файлов одинаковых по структуре, но разных по наполнению не так уж и сложно. Но что делать, если количество объединяемых файлов больше тысячи? Расскажем о том, как используя Power Query справиться с этой задачей.

Для объединения подходят многие файлы с данными. Обычно это результаты выгрузки из автоматизированных систем.

Файлы могут быть в формате txt, csv, xml, xlsx. C текстовыми файлами txt, csv никаких проблем нет, данные просто будут в одной общей таблице.

C xml-файлами немного сложнее, так как их структура может быть непредсказуема (многоуровневая табличная архитектура), но, в основном, надстройка PowerQuery легко справляется и с ними.

Кроме того, мы можем ещё до объединения файлов сделать обработку.

Например:

удалить или отфильтровать столбцы,
разбить столбец на несколько,
объединить несколько столбцов в один,
удалить лишние пробелы в столбце.

Можно даже изменить тип данных столбца, если это необходимо. Следует отметить, что данная настройка есть по умолчанию в MS Excel с версии 2016 года.

Как все это работает?

Допустим у нас есть папка с файлами, которые необходимо объединить в один. Первым делом, мы открываем пустой файл xlsx.

В верхнем меню выбираем «Данные», затем «Создать запрос» и указываем нашу папку с файлами.

Нажимаем объединить и изменить

Нажимаем кнопку ОК и работаем с предварительным просмотром, где можно производить редактирование таблицы.

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

Ещё одна, очень важная, возможность PowerQuery — объединение нескольких запросов.

Например, мы первым запросом объединили несколько файлов, вторым объединили другие файлы. Тогда можно их соединить, при наличии у них одинакового поля. Следует отметить, что поддерживается правое, левое и полное соединение.

Обзор объединения файлов

С помощью Power Query можно объединить несколько файлов с одной схемой в одну логическую таблицу.

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

Файлы могут поступать из различных источников, таких как (но не ограничено):

Локальные папки
Сайты SharePoint
Хранилище BLOB-объектов Azure
Azure Data Lake Storage (1-го и 2-го поколения)

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

File system view.

В представлении файловой системы столбец Content содержит двоичное представление каждого файла.

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

При выборе любого из значений [двоичного файла] в столбце содержимого автоматически создается ряд шагов навигации для этого конкретного файла. Power Query попытается интерпретировать двоичный файл с помощью одного из доступных соединителей, таких как Text/CSV, Excel, JSON или XML.

Объединение файлов выполняется на следующих этапах:

Предварительная версия таблицы
Диалоговое окно «Объединение файлов»
Выходные данные объединенных файлов

Просмотр таблицы

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

Table preview with the Combine option.

Кроме того, можно выбрать преобразование данных для доступа к Редактор Power Query и создать подмножество списка файлов (например, с помощью фильтров в столбце пути к папке для включения только файлов из определенной вложенной папки). Затем объедините файлы, выбрав столбец, содержащий двоичные файлы в столбце «Содержимое «, а затем выбрав один из следующих вариантов:

Команда «Объединить файлы» в группе «Объединение» на вкладке «Главная«.
Значок «Объединить файлы » в заголовке столбца столбца, содержащего значения [Binary].

Диалоговое окно «Объединение файлов»

После выбора команды «Объединить или объединить файлы» откроется диалоговое окно «Объединение файлов«, а также происходит следующее:

Power Query анализирует пример файла (по умолчанию первый файл в списке) и определяет правильный соединитель файлов для открытия этого файла.
Диалоговое окно предоставляет интерфейс соединителя файлов точно так же, как если бы вы подключились непосредственно к данному примеру файла.
- Если вы хотите использовать другой файл для примера файла, его можно выбрать в раскрывающемся меню «Пример файла «.
- Необязательно. Вы можете выбрать «Пропустить файлы с ошибками «, чтобы исключить из окончательного вывода все файлы, которые приводят к ошибкам.

На следующем рисунке Power Query обнаружил, что первый файл имеет расширение имени CSV-файла, поэтому он использует соединитель Text/CSV для интерпретации файла.

Combine files dialog box.

Выходные данные объединенных файлов

После завершения процесса объединения файлов Power Query автоматически выполняет следующие действия:

Combined files output.

Создает пример запроса, выполняющего все необходимые шаги извлечения для одного файла. В нем используется файл, выбранный в качестве примера файла в диалоговом окне «Объединение файлов «. В этом примере запроса есть файл преобразования примера имени в области «Запросы«.
Создает запрос функции, который параметризирует входные данные файла или двоичного файла в пример запроса. Пример запроса и запроса функции связаны, поэтому изменения в примере запроса отражаются в запросе функции. Эти запросы перечислены в группе вспомогательных запросов.
Применяет запрос функции к исходному запросу с входными двоичными файлами (например, запрос папки), поэтому он применяет запрос функции для двоичных входных данных для каждой строки, а затем расширяет результирующее извлечение данных в виде столбцов верхнего уровня.
Создает новую группу с файлом преобразования префикса и исходным запросом в качестве суффикса и упорядочивает все компоненты, используемые для создания этих объединенных файлов в этой группе.

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

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

Если какие-либо изменения влияют на имена столбцов или типы данных столбцов, обязательно проверка последнем шаге выходного запроса. Добавление шага типа столбца «Изменение» может привести к ошибке уровня шага, которая не позволяет визуализировать таблицу. Дополнительные сведения: работа с ошибками

Объединение файлов (двоичных файлов) в Power BI Desktop

Ниже приведен эффективный подход к импорту данных в Power BI Desktop: если у вас несколько файлов с одной схемой, объединить их в одну логическую таблицу. Эта популярная техника была сделана более удобной и более обширной.

Чтобы начать процесс объединения файлов из одной папки, выберите «Получить данные», выберите «Папка файла>«, а затем выберите Подключение.

Screenshot of the Get Data dialog box highlighting the choice to connect to a folder.

Введите путь к папке, нажмите кнопку «ОК«, а затем нажмите кнопку «Преобразовать данные«, чтобы просмотреть файлы папки в Редактор Power Query.

Объединение поведения файлов

Чтобы объединить двоичные файлы в Редактор Power Query, выберите «Содержимое» (первая метка столбца) и выберите «Главная >объединение файлов». Также можно просто выбрать значок «Объединить файлы» рядом с содержимым.

Screenshot of the Query Editor highlighting the icon to combine files.

Преобразование объединенных файлов ведет себя следующим образом:

Преобразование объединения файлов анализирует каждый входной файл, чтобы определить правильный формат файлов, используемый, например текст, книгу Excel или JSON-файл.
Преобразование позволяет выбрать конкретный объект из первого файла, например книгу Excel, чтобы извлечь его.

Screenshot of the Combine Files dialog box in Power BI desktop.

Screenshot of the results of the combine files transform.

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

Область выбора в книге Excel повлияет на поведение объединения двоичных файлов. Например, можно выбрать определенный лист, чтобы объединить этот лист, или выбрать корень, чтобы объединить полный файл. Выбор папки объединяет файлы, найденные в этой папке.

Благодаря поведению объединенных файлов можно легко объединить все файлы в заданной папке, если они имеют одинаковый тип и структуру файлов (например, одни и те же столбцы).

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

Связанный контент

Вы можете подключиться ко всем типам данных с помощью Power BI Desktop. Дополнительные сведения о источниках данных см. в следующих ресурсах:

Что такое Power BI Desktop?
Data sources in Power BI Desktop (Источники данных в Power BI Desktop)
Формирование и объединение данных с Power BI Desktop
Подключение CSV-файлы в Power BI Desktop
Ввод данных непосредственно в Power BI Desktop