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