Імпорт даних із папки з кількома файлами (Power Query)

Використовуйте Power Query, щоб об'єднати кілька файлів з однією схемою, що зберігається в одній папці, в одну таблицю. Наприклад, щомісяця потрібно об'єднувати бюджетні книги з кількох відділів, у яких стовпці однакові, але кількість рядків і значень у кожній книзі відрізняється. Налаштувавши її, ви можете застосовувати додаткові перетворення так само, як і з будь-яким джерелом даних, що імпортується, а потім оновлювати дані , щоб переглядати результати за кожен місяць.

Концептуальний огляд об'єднання файлів папок

Примітка. У цій статті описано, як об'єднувати файли з папки. Ви також можете об'єднати файли, які зберігаються в SharePoint, сховищі BLOB-об'єктів Azure та Azure Data Lake Storage. Процес схожий.

Новіші версії Office 2013



Не заскладніть цю функцію.

Переконайтеся, що всі файли, які потрібно об'єднати, містяться в виділеній папці без зайвих файлів. В іншому разі всі файли в папці та вкладені папки додаються до даних, які потрібно об'єднати.
Кожен файл має мати однакову схему з узгодженими заголовками стовпців, типами даних і кількістю стовпців. Стовпці не обов'язково мають збігатися з іменами стовпців.
Якщо це можливо, уникайте непов'язаних об'єктів даних для джерел даних, які можуть містити кілька об'єктів даних, наприклад JSON-файл, книгу Excel або базу даних Access.

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

Виберіть пункт Дані > Отримати дані > з файлу > з папки. Відкриється діалогове вікно Огляд .
Знайдіть папку з файлами, які потрібно об'єднати.
Список файлів у папці з'явиться в діалоговому вікні> шлях до папки <. Переконайтеся, що всі потрібні файли наведено в списку.
Виберіть одну з команд у нижній частині діалогового вікна, наприклад Об'єднати > Об'єднати & Завантажити. У розділі Про всі ці команди описано додаткові команди.
Якщо вибрати будь-яку команду Об'єднати , відкриється діалогове вікно Об'єднання файлів . Щоб змінити параметри файлу, виберіть кожен файл у полі Зразок файлу , задайте потрібні параметри "Джерело файлу", "Роздільник" і "Виявлення типів даних ". Також можна встановити або зняти прапорець Пропустити файли з помилками в нижній частині діалогового вікна.
Натисніть кнопку OK.

Результат

Power Query автоматично створює запити для об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.

Виберіть пункт Дані > Отримати дані > з файлу > з папки. Відкриється діалогове вікно Огляд.
Знайдіть папку з файлами, які потрібно об'єднати.
Список файлів у папці з'явиться в діалоговому вікні> шлях до папки <. Переконайтеся, що всі потрібні файли наведено в списку.
Виберіть одну з команд у нижній частині діалогового вікна, наприклад Об'єднати > Об'єднати & Перетворення. У розділі Про всі ці команди описано додаткові команди.

З'явиться Редактор Power Query.
Стовпець Значення – це стовпець структурованого списку . Клацніть піктограму Розгорнути , а потім виберіть Розгорнути до нових рядків.
Стовпець "Значення" тепер є структурованим стовпцем "Запис ". Клацніть піктограму Розгорнути . Відкриється діалогове вікно розкривного списку.
Залишити виділеними всі стовпці. Можна зняти прапорець Використовувати вихідне ім'я стовпця як префікс . Натисніть кнопку OK.
Виділіть усі стовпці, які містять значення даних. На вкладці Основне клацніть стрілку поруч із кнопкою Видалити стовпці, а потім виберіть видалити інші стовпці.
Натисніть кнопку Основне > Закрити & Завантажити.

Результат

Кожне з цих джерел даних може мати кілька об'єктів для імпорту. Книга Excel може містити кілька аркушів, таблиць Excel або іменованих діапазонів. База даних Access може містити кілька таблиць і запитів.

Виберіть пункт Дані > Отримати дані > з файлу > з папки. Відкриється діалогове вікно Огляд.
Знайдіть папку з файлами, які потрібно об'єднати.
Список файлів у папці з'явиться в діалоговому вікні> шлях до папки <. Переконайтеся, що всі потрібні файли наведено в списку.
Виберіть одну з команд у нижній частині діалогового вікна, наприклад Об'єднати > Об'єднати & Завантажити. У розділі Про всі ці команди описано додаткові команди.
У діалоговому вікні Об'єднання файлів виконайте такі дії:
- У полі Зразок файлу виберіть файл, який використовуватиметься як зразок даних для створення запитів. Не можна виділити об'єкт або виділити лише один об'єкт. Але вибрати кілька елементів не можна.
- Якщо об'єктів багато, скористайтеся полем пошуку , щоб знайти об'єкт або параметри відображення , а також кнопку Оновити , щоб відфільтрувати список.
- Установіть або зніміть прапорець Пропустити файли з помилками в нижній частині діалогового вікна.
Натисніть кнопку OK.

Результат

Power Query автоматично створює запит на об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.

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

Виберіть елемент Дані > Отримати > данихіз файлу > з папки. Відкриється діалогове вікно Огляд .
Знайдіть папку з файлами, які потрібно об'єднати, і натисніть кнопку Відкрити.
Список усіх файлів у папці та вкладених папках відображається в діалоговому вікні >шлях до папки< . Переконайтеся, що всі потрібні файли наведено в списку.
Натисніть кнопку Перетворення даних у нижній частині екрана. Відкриється Редактор Power Query з усіма файлами в папці та вкладених папках.
Щоб вибрати потрібні файли, відфільтруйте стовпці, наприклад Розширення або Шлях до папки.
Щоб об'єднати файли в одну таблицю, виділіть стовпець Вміст , який містить кожен двійковий файл (зазвичай це перший стовпець), а потім виберіть Основне > Об'єднати файли. Відкриється діалогове вікно Об'єднання файлів .
Power Query аналізує приклад файлу, за замовчуванням перший файл у списку, щоб використовувати правильний з'єднувач і визначати відповідні стовпці.

Щоб використати інший файл для прикладу файлу, виберіть його з розкривного списку Зразок файлу .
За потреби в нижній частині виберіть пропустити файли з помилкоюs, щоб виключити ці файли з результату.
Натисніть кнопку OK.

Результат

Є кілька команд, які можна вибрати, і кожна з них має різні призначення.

Об'єднання та перетворення даних Щоб поєднати всі файли із запитом, а потім запустити Редактор Power Query, виберіть Об'єднати > Об'єднати та перетворити дані.
Об'єднати та завантажити Щоб відкрити діалогове вікно Зразок файлу, створіть запит, а потім завантажте його до аркуша, а потім натисніть кнопку Об'єднати > Об'єднати та завантажити.
Об'єднати та завантажити до Щоб відкрити діалогове вікно Зразок файлу, створіть запит, а потім відобразіть діалогове вікно Імпорт , виберіть пункт Об'єднати > Об'єднати та завантажити до.
Навантаження Щоб створити запит одним кроком, а потім завантажити його до аркуша, натисніть кнопку Завантажити > Завантажити.
Завантажити до Щоб створити запит одним кроком, а потім відкрити діалогове вікно Імпорт , натисніть кнопку Завантажити > Завантажити до.
Перетворення данихЩоб створити запит одним кроком, а потім запустити Редактор Power Query, натисніть кнопку Перетворити дані.

Проте ви об'єднуєте файли, в області Запити в групі "Довідкові запити" створюються кілька допоміжних запитів.

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

Power Query створює запит "Зразок файлу" на основі прикладу запиту.
У запиті функції "Файл перетворення" використовується запит "Параметр1", щоб указати кожен файл (або двійковий файл) як вхідні дані в запиті "Зразок файлу". Цей запит також створює стовпець Вміст , що містить вміст файлу, і автоматично розширює структурований стовпець Запис, щоб додати дані стовпця до результатів. Запити "Файл перетворення" та "Зразок файлу" зв'язані, тому зміни в запиті "Зразок файлу" відображаються в запиті "Файл перетворення".
Запит, який містить остаточні результати, міститься в групі "Інші запити". За замовчуванням він називається на честь папки, з якої імпортовано файли.

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

Щоб запустити процес об’єднання кількох файлів, спочатку помістіть їх в одну папку.

Примітка.: Підтримуються текстові файли, файли Excel і Access, а також файли формату CSV, JSON і XML.

Перейдіть на вкладку Power Query, а потім виберіть З файлу >З папки.
Натисніть кнопку Огляд і виберіть потрібну папку.

З’явиться список файлів, збережених у цій папці. Щоб відкрити редактор запитів, виберіть Редагувати. У нашому випадку ми завантажили 4 книги Excel.
Перевірте, чи містить список усі потрібні файли, а потім у стовпці Content натисніть кнопку Об’єднати двійкові дані
Excel проаналізує кожен файл і визначить, чи правильний у нього формат (підтримується текстовий формат, Excel або JSON). У нашому випадку з’явився список аркушів із першої книги Excel. Виберіть потрібний аркуш і натисніть кнопку OK.
Після цього автоматично створиться запит на об’єднання даних із кожного файлу й з’явиться область попереднього перегляду. Якщо ви задоволені результатом, виберіть Закрити й завантажити.

Процес об’єднання двійкових даних дає змогу об’єднати дані аркушів зі списку в один аркуш.
Якщо вихідні файли даних зміняться, ви завжди можете оновити імпортовані дані. Клацніть будь-де в діапазоні даних, а потім перейдіть до розділу Знаряддя для запитів > Оновити. Крім того, ви можете легко застосувати додаткові кроки перетворення або видобування, змінивши автоматично створений зразковий запит, не переймаючись зміненням або створенням додаткових кроків запиту функції; будь-які зміни в зразкових запитах автоматично створюються в запиті зв'язаної функції.

Відомі проблеми

Коли ви об’єднуєте двійкові дані в Excel, може з’явитися таке повідомлення про помилку:

Повідомлення про помилку об’єднання двійкових файлів. Це відома проблема, яку вирішено.

Якщо закрити повідомлення (натиснути кнопку OK), об’єднання двійкових даних завершиться, а всі дані повністю завантажаться належним чином. Ми працюємо над вирішенням цієї проблеми.