Используйте Power Query для объединения нескольких файлов с одной схемой, хранящейся в одной папке, в одну таблицу. Например, каждый месяц требуется объединять бюджетные книги из нескольких отделов, где столбцы одинаковы, но количество строк и значений в каждой книге отличается. После настройки можно применить дополнительные преобразования, как и к любому отдельному импортированному источнику данных, а затем обновить данные , чтобы просмотреть результаты за каждый месяц.
Примечание В этом разделе показано, как объединить файлы из папки. Вы также можете объединять файлы, хранящиеся в SharePoint, Хранилище BLOB-объектов Azure и Azure Data Lake Storage. Процесс аналогичен.
Подготовка
Не усложняйте.
- Убедитесь, что все файлы, которые требуется объединить, содержатся в выделенной папке без лишних файлов. В противном случае все файлы в папке и все выбираемые вложенные папки включаются в объединенные данные.
- Каждый файл должен иметь одинаковую схему с согласованными заголовками столбцов, типами данных и количеством столбцов. Столбцы не должны находиться в том же порядке, в котором сопоставление выполняется по именам столбцов.
- По возможности избегайте несвязанных объектов данных для источников данных, которые могут содержать несколько объектов данных, таких как JSON-файл, книга Excel или база данных Access.
Импорт из текстовых, CSV-файлов или XML-файлов
Каждый из этих файлов соответствует простой схеме: в каждом файле содержится только одна таблица данных.
Выберите DataGet Data FromFile from Folder (Получить> данные >из файла>из папки). Откроется диалоговое окно Обзор .
Найдите папку, содержащую файлы, которые требуется объединить.
В диалоговом окне Путь> к папке появится список файлов в папке<. Убедитесь, что перечислены все нужные файлы.
Выберите одну из команд в нижней части диалогового окна, например Объединить>& Загрузить. В разделе Обо всех этих командах рассматриваются дополнительные команды.
Если выбрать любую команду Объединить, откроется диалоговое окно Объединение Files. Чтобы изменить параметры файла, выберите каждый файл в поле Пример файла , задайте для параметра Источник файла, Разделитель и Обнаружение типа данных по желанию. Можно также установить или снять флажок Пропускать файлы с ошибками в нижней части диалогового окна.
Нажмите кнопку ОК.
Результат
Power Query автоматически создает запросы для консолидации данных из каждого файла на листе. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.
Импорт из JSON
Выберите DataGet Data FromFile from Folder (Получить> данные >из файла>из папки). Откроется диалоговое окно Обзор .
Найдите папку, содержащую файлы, которые требуется объединить.
В диалоговом окне Путь> к папке появится список файлов в папке<. Убедитесь, что перечислены все нужные файлы.
Выберите одну из команд в нижней части диалогового окна, например Объединить>объединение & преобразование. В разделе Обо всех этих командах рассматриваются дополнительные команды.
Появится Редактор Power Query.
Столбец Значение — это структурированный столбец List . Щелкните
, а затем выберите Развернуть до новых строк. 
Столбец Значение теперь является структурированным столбцом Запись . Щелкните
. Откроется раскрывающееся диалоговое окно.
Оставьте все столбцы выбранными. Может потребоваться очистить поле Использовать имя исходного столбца в качестве префикса проверка. Нажмите кнопку ОК.
Выберите все столбцы, содержащие значения данных. Выберите Главная, стрелку рядом с пунктом Удалить столбцы, а затем выберите Удалить другие столбцы.
Выберите Главная>Закрыть & Загрузить.
Результат
Power Query автоматически создает запросы для консолидации данных из каждого файла на листе. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.
Импорт из Excel или Access
Каждый из этих источников данных может иметь несколько объектов для импорта. Книга Excel может содержать несколько листов, таблиц Excel или именованных диапазонов. База данных Access может содержать несколько таблиц и запросов.
Выберите DataGet Data FromFile from Folder (Получить> данные >из файла>из папки). Откроется диалоговое окно Обзор .
Найдите папку, содержащую файлы, которые требуется объединить.
В диалоговом окне Путь> к папке появится список файлов в папке<. Убедитесь, что перечислены все нужные файлы.
Выберите одну из команд в нижней части диалогового окна, например Объединить>& Загрузить. В разделе Обо всех этих командах рассматриваются дополнительные команды.
В диалоговом окне Объединение Files выполните следующие действия:
- В поле Пример файла выберите файл, который будет использоваться в качестве примера данных, используемых для создания запросов. Вы не можете выбрать объект или выбрать только один объект. Но вы не можете выбрать более одного.
- Если у вас много объектов, используйте поле Поиск , чтобы найти объект, или параметры отображения вместе с кнопкой Обновить , чтобы отфильтровать список.
- Установите или снимите флажок Пропускать файлы с ошибками в нижней части диалогового окна.
Нажмите кнопку ОК.
Результат
Power Query автоматически создает запрос для объединения данных из каждого файла в лист. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.
Использование команды Combine Files
Для большей гибкости можно явно объединить файлы в Редактор Power Query с помощью команды Combine Files. Предположим, исходная папка содержит сочетание типов файлов и вложенных папок, и вы хотите выбрать конкретные файлы с тем же типом файлов и схемой, но не с другими. Это может повысить производительность и упростить преобразования.
Выберите DataGet Data FromFile from Folder (Получить> данные >из файла>из папки). Откроется диалоговое окно Обзор .
Найдите папку, содержащую файлы, которые требуется объединить, и нажмите кнопку Открыть.
В диалоговом окне Путь> к папке появится список всех файлов в папке и вложенных папках<. Убедитесь, что перечислены все нужные файлы.
Выберите Преобразовать данные в нижней части экрана. Откроется Редактор Power Query, в котором отображаются все файлы в папке и все вложенные папки.
Чтобы выбрать нужные файлы, отфильтруйте столбцы, например Расширение или Путь к папке.
Чтобы объединить файлы в одну таблицу, выберите столбец Содержимое, содержащий каждый двоичный файл (обычно первый столбец), а затем выберите Главная>объединение Files. Откроется диалоговое окно Объединение Files.
Power Query анализирует пример файла, по умолчанию первый файл в списке, чтобы использовать правильный соединитель и определить соответствующие столбцы.
Чтобы использовать другой файл для примера файла, выберите его в раскрывающемся списке Пример файла .
При необходимости в нижней части экрана выберите Пропустить файлы с ошибками , чтобы исключить эти файлы из результата.
Нажмите кнопку ОК.
Результат
Power Query автоматически создает запросы для консолидации данных из каждого файла в лист. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.
Обо всех этих командах
Существует несколько команд, которые можно выбрать, и каждая из них имеет разные цели.
- Объединение и преобразование данных Чтобы объединить все файлы с запросом, а затем запустить Редактор Power Query, выберите объединить>и преобразовать данные.
- Объединение и загрузка Чтобы открыть диалоговое окно Пример файла, создайте запрос, а затем загрузите на лист, выберите Объединить>объединить и загрузить.
- Объединение и загрузка в Чтобы открыть диалоговое окно Пример файла, создайте запрос, а затем откройте диалоговое окно Импорт , выберите Объединить>объединить и Загрузить в.
- Нагрузки Чтобы создать запрос с одним шагом, а затем загрузить на лист, выберите Загрузить>.
- Загрузка в Чтобы создать запрос с одним шагом, а затем открыть диалоговое окно Импорт , выберите Загрузить>в.
- Преобразование данных Чтобы создать запрос с одним шагом, а затем запустить Редактор Power Query, выберите Преобразовать данные.
Обо всех этих запросах
Однако вы объединяете файлы, несколько вспомогательных запросов создаются в области Запросы в группе "Вспомогательные запросы".
- Power Query создает запрос "Образец файла" на основе примера запроса.
- Запрос функции Transform File использует запрос Parameter1, чтобы указать каждый файл (или двоичный файл) в качестве входных данных для запроса Sample File. Этот запрос также создает столбец Содержимое , содержащий содержимое файла, и автоматически расширяет структурированный столбец Запись , чтобы добавить данные столбца в результаты. Запросы "Файл преобразования" и "Пример файла" связаны, поэтому изменения в запросе "Пример файла" отражаются в запросе "Файл преобразования".
- Запрос, содержащий окончательные результаты, находится в группе "Другие запросы". По умолчанию он называется в честь папки, из которую вы импортировали файлы.
Для дальнейшего изучения щелкните правой кнопкой мыши каждый запрос и выберите Изменить , чтобы изучить каждый шаг запроса и посмотреть, как запросы работают согласованно.
См. также
Справка по Power Query для Excel