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

Концептуальный обзор объединения файлов папок

Примечание.    В этой теме показано, как объединять файлы из папки. Вы также можете объединять файлы, хранимые в SharePoint, azure BLOB-служба хранилища и Azure Data Lake служба хранилища. Процесс аналогичный.

Не изумять:

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

  • У каждого файла должна быть та же схема, что и для согласованных столбцов, типов данных и количества столбцов. Столбцы не должны быть в том же порядке, что и имена столбцов.

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

Каждый из этих файлов имеет простой шаблон, в каждом из которых есть только одна таблица данных.

  1. Выберите Данные > Получить данные > из файла > из папки. Появится диалоговое окно Обзор.

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

  3. Список файлов в папке появится в диалоговом <путь к> папке. Убедитесь, что в списке указаны все нужные файлы.

    Пример диалогового окна импорта текста

  4. Выберите одну из команд в нижней части диалогового окна, например Объединить> Объединить & Загрузить. В разделе Обо всех этих командах обсуждаются дополнительные команды.

  5. Если выбрать команду Объединить, появится диалоговое окно Объединение файлов. Чтобы изменить параметры файла, выберите каждый файл в поле Образец файла, задав нужные параметры Источник файла,Делитер и Обнаружение типов данных. Кроме того, в нижней части диалогового окна можно выбрать или отобрать диалоговое окно Пропускать файлы с ошибками.

  6. Нажмите кнопку ОК.

Результат

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

  1. Выберите Данные > Получить данные > из файла > из папки. Появится диалоговое окно Обзор.

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

  3. Список файлов в папке появится в диалоговом <путь к> папке. Убедитесь, что в списке указаны все нужные файлы.

  4. Выберите одну из команд в нижней части диалогового окна, например Объединить> Объединить & Transform. В разделе Обо всех этих командах обсуждаются дополнительные команды.

    Появится редактор Power Query.

  5. Столбец Значение является структурированным столбцом списка. Выберите значок Развернуть Значок развертывания столбца , а затем выберите Развернуть до новых строк. 

    Расширение списка JSON

  6. Столбец Value (Значение) теперь является структурированным столбцом Record (Запись).  Выберите значок Значок развертывания столбца развернуть. Появится диалоговое окно с drop-down.

    Расширение записи JSON

  7. Не избирать все столбцы. Возможно, вам нужно будет очистить поле Использовать имя исходного столбца в качестве префикса. Нажмите кнопку ОК.

  8. Вы можете выбрать все столбцы, содержащие значения данных. На лентевыберите Главная , стрелку рядом с клавишей Remove Columns, а затем выберите Удалить другие столбцы.

  9. Выберите Главная> закрыть & загрузить.

Результат

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

Каждый из этих источников данных может иметь несколько объектов для импорта. Книга Excel может иметь несколько книг, Excel таблиц или именовых диапазонов. База данных Access может иметь несколько таблиц и запросов. 

  1. Выберите Данные > Получить данные > из файла > из папки. Появится диалоговое окно Обзор.

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

  3. Список файлов в папке появится в диалоговом <путь к> папке. Убедитесь, что в списке указаны все нужные файлы.

  4. Выберите одну из команд в нижней части диалогового окна, например Объединить> Объединить & Загрузить. В разделе Обо всех этих командах обсуждаются дополнительные команды.

  5. В диалоговом окне Объединение файлов:

    • В поле Образец файла выберите файл, который будет использоваться в качестве примера данных для создания запросов. Нельзя выбрать объект или выбрать только один объект. Однако выбрать несколько из них нельзя.

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

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

  6. Нажмите кнопку ОК.

Результат

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

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

  1. Выберите data > Get Data > Data > From File > From Folder. Появится диалоговое окно Обзор.

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

  3. Список всех файлов в папке и вложенных папках появится в диалоговом<путь>папки. Убедитесь, что в списке указаны все нужные файлы.

  4. Внизу выберите преобразовать данные. Откроется редактор Power Query со всеми файлами в папке и во вложенных папках.

  5. Чтобы выбрать нужные файлы, отфильтруем столбцы, например Расширение или Путь к папке.

  6. Чтобы объединить файлы в одну таблицу, выберите столбец Содержимое, содержащий каждый двоичный (обычно первый столбец), а затем выберите Главная > Объединить файлы. Появится диалоговое окно Объединение файлов.

  7. Power Query анализирует пример файла (по умолчанию первый файл в списке), чтобы использовать правильный соединительщик и определить совпадающие столбцы.

    Чтобы использовать другой файл для примера, выберите его в списке Образец файла.

  8. При желании внизу выберите пропустить файлы с ошибкой s,чтобы исключить эти файлы из результата.

  9. Нажмите кнопку ОК.

Результат

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

Существует несколько команд, которые можно выбрать, и каждая из них имеет разные цели.

  • Объединение и преобразование данных    Чтобы объединить все файлы с запросом, а затем запустить редактор Power Query, выберите объединить> объединить и преобразовать данные.

  • Объединение и загрузка   Чтобы отобразить диалоговое окно Образец файла, создайте запрос, а затем загрузите на таблицу, выберите объединить> Объединить и загрузить.

  • Объединение и загрузка в   Чтобы отобразить диалоговое окно Образец файла, создайте запрос, а затем в диалоговом окне Импорт выберите объединить> Объединить и загрузить в.

  • Нагрузки    Чтобы создать запрос с одним шагом, а затем загрузить на таблицу, выберите загрузить > загрузить.

  • Загрузить в   Чтобы создать запрос одним шагом и отобразить диалоговое окно Импорт, выберите загрузить > Загрузить в.

  • Преобразование данныхЧтобы создать запрос с одним шагом и запустить редактор Power Query, выберите Преобразовать данные.

Тем не менее при объединения файлов в области Запросы в группе "Запросы-справки" создается несколько вспомогательных запросов.

Список запросов, созданных в области "Запросы"

  • Power Query создает запрос "Образец файла" на основе примера запроса.

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

  • Запрос, содержащий окончательные результаты, находится в группе "Другие запросы". По умолчанию он называется папкой, из которого вы импортировали файлы.

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

Чтобы начать процесс объединения нескольких файлов, сначала поместите их все в одну папку.

Примечание: Поддерживаются файлы Excel и Access, текстовые файлы, а также файлы в форматах CSV, JSON и XML.

  1. На вкладке Power Query выберите из файла > из папки.

    Power Query > параметры "Из > из папки"
  2. Нажмите кнопку Обзор, чтобы перейти к нужной папке.

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

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

    Нажмите кнопку "Объединить двоичные данные" в столбце "Содержимое" или в разделе "Объединить" на ленте Power Query.
  4. Будет выполнен анализ каждого файла и определен правильный формат, например текстовый, Excel или JSON. В этом примере отображается список листов из первой книги Excel. Выберите нужный лист и нажмите кнопку ОК.

    Диалоговое окно объединения двоичных файлов, в котором показаны листы Excel, доступные для выбора в качестве основной цели для размещения объединенных данных
  5. При преобразовании разных файлов автоматически создается запрос для консолидации данных из каждого файла и предварительного просмотра. Если результат вас устраивает, нажмите кнопку Закрыть & загрузить.

    Диалоговое окно для предварительного просмотра результатов объединения. Нажмите "Закрыть и загрузить", чтобы принять результаты и импортировать их в Excel.

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

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

Известные проблемы

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

Сообщение об ошибке при объединении двоичных файлов. Это известная ошибка, которая будет устранена.

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

См. также

Справка по Power Query для Excel

Добавление запросов

Обзор объединения файлов (docs.com)

Объединение CSV-файлов в Power Query (docs.com)

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

Спасибо за ваш отзыв!

×