Використовуйте Power Query, щоб об'єднати кілька файлів з однаковою схемою, що зберігаються в одній папці, в одну таблицю. Наприклад, щомісяця потрібно об'єднати бюджетні книги кількох відділів з однаковою кількістю стовпців, але з різною кількістю рядків і значень. Настроївши передплату, можна застосувати додаткові перетворення так само, як і для будь-якого імпортованого джерела даних, а потім оновити дані , щоб побачити результати за кожний місяць.
Примітка У цій статті показано, як об'єднати файли в одній папці. Ви також можете об'єднати файли, які зберігаються в SharePoint, AzureAzure Blob-Storage і Azure Data Lake StorageAzure Data Lake Storage. Процес аналогічний.
Підготовка
Будьте простими.
- Переконайтеся, що всі файли, які ви хочете об'єднати, містяться в спеціальній папці без сторонніх файлів. В іншому разі всі файли у вибраній папці та у вкладених папках включаються до даних, що об'єднуються.
- У кожного файлу має бути однакова схема з узгодженими заголовками стовпців, типами даних і кількістю стовпців. Стовпці не обов'язково мають розташовуватися в тому ж порядку, у якому зіставляються імена стовпців.
- Якщо можливо, намагайтеся уникати непов'язаних об'єктів даних у джерелах даних, які можуть містити кілька об'єктів, як-от файл JSON, книга Excel або база даних Access.
Імпорт із текстових, CSV- або XML-файлів
Кожен із цих файлів працює за простою схемою: лише одна таблиця даних у кожному файлі.
Виберіть пункт "Дані>", "Отримати дані>з файлу>з папки". Відобразиться діалогове вікно "Огляд ".
Знайдіть папку, що містить файли, які потрібно об'єднати.
Список файлів у цій папці відобразиться в <діалоговому вікні "Шлях до> папки". Переконайтеся, що в списку є всі потрібні файли.
Виберіть одну з команд у нижній частині діалогового вікна, наприклад "Об'єднати", "Об'єднати>" & "Завантажити". Докладніші відомості містяться й інші команди, описані в розділі Про всі ці команди.
Якщо вибрати будь-яку команду "Об'єднати", відкриється діалогове вікно "Об'єднати Files". Щоб змінити параметри файлу, виберіть кожен файл у полі " Зразок файлу " та за потреби встановіть походження файлу, роздільник і виявлення типу даних . Також можна встановити або зняти прапорець "Пропускати файли з помилками" в нижній частині діалогового вікна.
Натисніть кнопку OK.
Результат
Power QueryНадбудова Power Query автоматично створює запити для об'єднання даних із кожного файлу в аркуш. Кроки запиту та створені стовпці залежать від вибраної команди. Докладніші відомості див. в розділі "Про всі ці запити".
Імпорт із JSON
Виберіть пункт "Дані>", "Отримати дані>з файлу>з папки". Відобразиться діалогове вікно "Огляд ".
Знайдіть папку, що містить файли, які потрібно об'єднати.
Список файлів у цій папці відобразиться в <діалоговому вікні "Шлях до> папки". Переконайтеся, що в списку є всі потрібні файли.
Виберіть одну з команд у нижній частині діалогового вікна, наприклад "Об'єднати", "Об'єднати>" & "Перетворити". Докладніші відомості містяться й інші команди, описані в розділі Про всі ці команди.
З'явиться Редактор Power Query.
Стовпець "Значення" – це стовпець структурованого списку . Клацніть піктограму " Розгорнути
" та виберіть пункт "Розгорнути до нових рядків". 
Тепер стовпець "Значення" є структурованим стовпцем запису . Клацніть піктограму "Розгорнути"
". З'явиться розкривне діалогове вікно.
Залиште всі стовпці виділеними. Може знадобитися зняти прапорець "Використовувати вихідне ім'я стовпця як префікс". Натисніть кнопку OK.
Виберіть усі стовпці, які містять значення даних. Виберіть вкладку "Основне", потім клацніть стрілку поруч із пунктом "Видалити стовпці", а потім виберіть пункт "Видалити інші стовпці".
Виберіть Головна>сторінка Закрити & Завантажити.
Результат
Power QueryНадбудова Power Query автоматично створює запити для об'єднання даних із кожного файлу в аркуш. Кроки запиту та створені стовпці залежать від вибраної команди. Докладніші відомості див. в розділі "Про всі ці запити".
Імпорт з Excel або Access
Кожне з цих джерел даних може містити кілька об'єктів для імпорту. Книга Excel може містити кілька аркушів, таблиць Excel або іменованих діапазонів. База даних Access може містити кілька таблиць і запитів.
Виберіть пункт "Дані>", "Отримати дані>з файлу>з папки". Відобразиться діалогове вікно "Огляд ".
Знайдіть папку, що містить файли, які потрібно об'єднати.
Список файлів у цій папці відобразиться в <діалоговому вікні "Шлях до> папки". Переконайтеся, що в списку є всі потрібні файли.
Виберіть одну з команд у нижній частині діалогового вікна, наприклад "Об'єднати", "Об'єднати>" & "Завантажити". Докладніші відомості містяться й інші команди, описані в розділі Про всі ці команди.
У діалоговому вікні "Об'єднання FilesFiles" виконайте такі дії:
- У полі "Зразок файлу " виберіть файл, який потрібно використати як зразок даних для створення запитів. Можна або не виділяти об'єкти, або виділити лише один об'єкт. Проте кілька таких облікових записів вибрати не можна.
- Якщо об'єктів забагато, знайдіть об'єкт за допомогою поля пошуку або параметрів відображення , а потім натисніть кнопку " Оновити ", щоб відфільтрувати список.
- Установіть або зніміть прапорець "Пропускати файли з помилками" в нижній частині діалогового вікна.
Натисніть кнопку OK.
Результат
Power QueryНадбудова Power Query автоматично створює запит на об'єднання даних із кожного файлу в аркуш. Кроки запиту та створені стовпці залежать від вибраної команди. Докладніші відомості див. в розділі "Про всі ці запити".
Використання команди Combine FilesFiles
Для більшої гнучкості ви можете явно об'єднувати файли в редакторі Power Query TРедактор Power Query за допомогою команди Combine FilesFiles. Припустімо, що вихідна папка поєднує в собі кілька типів файлів і вкладених папок, і вам потрібно вибрати окремі файли з однаковим типом файлу та схемою, але не з іншими. Це може покращити продуктивність і спростити перетворення.
Виберіть пункт "Дані>", "Отримати дані>з файлу>з папки". Відобразиться діалогове вікно "Огляд ".
Знайдіть папку, що містить файли, які потрібно об'єднати, і натисніть кнопку "Відкрити".
У діалоговому вікні "Шлях до папки>" з'явиться< список усіх файлів у цій і вкладених папках. Переконайтеся, що в списку є всі потрібні файли.
Унизу виберіть "Перетворити дані ". Відкриється Редактор Power Query зі всіма файлами в папці та вкладених папках.
Щоб вибрати потрібні файли, відфільтруйте стовпці, наприклад "Розширення" або "Шлях до папки".
Щоб об'єднати файли в одну таблицю, виберіть стовпець Content, який містить кожен двійковий файл (зазвичай це перший стовпець), а потім виберіть Home>Об'єднайте FilesFiles. З'явиться діалогове вікно "Об'єднати FilesFiles".
Power QueryНадбудова Power Query аналізує файл прикладу, за замовчуванням це перший файл у списку, щоб використати правильну сполучну лінію та визначити відповідні стовпці.
Щоб використати для прикладу інший файл, виберіть його з розкривного списку " Зразок файлу ".
За потреби внизу виберіть " Пропустити файли з помилками ", щоб виключити їх із результату.
Натисніть кнопку OK.
Результат
Power QueryНадбудова Power Query автоматично створює запити для об'єднання даних із кожного файлу в аркуш. Кроки запиту та створені стовпці залежать від вибраної команди. Докладніші відомості див. в розділі "Про всі ці запити".
Про всі ці команди
Існує кілька команд, які можна вибрати, і кожна з них має різне призначення.
- Групування й перетворення даних Щоб об'єднати всі файли за допомогою запиту, а потім запустити Редактор Power Query, виберіть ''Об'єднати>''й перетворити дані.
- Об'єднання та завантаження Щоб відкрити діалогове вікно "Зразок файлу", створіть запит, а потім завантажте його на аркуш, виберіть " Об'єднати, об'єднати>та завантажити".
- Об'єднання та завантаження Щоб відкрити діалогове вікно " Зразок файлу", створіть запит, а потім відкрийте діалогове вікно " Імпорт ", виберіть " Об'єднати>й завантажити до".
- Завантажити Щоб створити запит в один крок, а потім завантажити його на аркуш, виберіть " Завантажити>завантаження".
- Завантажити до. Щоб створити запит в один крок, а потім відобразити діалогове вікно "Імпорт ", виберіть команду "Завантажити>до".
- Перетворення даних Щоб створити запит одним кроком і запустити Редактор Power Query, виберіть "Transform Data" (Перетворити дані).
Про всі ці запити
Незалежно від того, як ви об'єднуєте файли, в області "Запити " в групі "Допоміжні запити" створюється кілька додаткових запитів.
- Power QueryНадбудова Power Query створює запит "Sample File" на основі запиту прикладу.
- Запит функції "Transform File" використовує запит "Parameter1", щоб вказати кожен файл (або двійковий) як вхідні дані для запиту "Sample File". Під час виконання цього запиту також створюється стовпець "Вміст", який містить вміст файлу, і автоматично розгортається структурований стовпець запису , щоб додати дані стовпця до результатів. Запити "Перетворити файл" і "Зразок файлу" пов'язано між собою, тому зміни запиту "Зразок файлу" відображаються в запиті "Перетворення файлу".
- Запит з остаточними результатами перебуває в групі «Інші запити». За замовчуванням воно називається на честь папки, з якої імпортовано файли.
Щоб переглянути подальші дії, клацніть кожен запит правою кнопкою миші та виберіть "Редагувати ", щоб перевірити кожен крок запиту та побачити узгодження його роботи.
Додаткові відомості
Power QueryДовідка з надбудови Power Query для програми Excel