Використовуйте Power Query, щоб об'єднати кілька файлів з однією схемою, що зберігається в одній папці, в одну таблицю. Наприклад, щомісяця потрібно об'єднувати бюджетні книги з кількох відділів, у яких стовпці однакові, але кількість рядків і значень у кожній книзі відрізняється. Налаштувавши її, ви можете застосовувати додаткові перетворення так само, як і з будь-яким джерелом даних, що імпортується, а потім оновлювати дані , щоб переглядати результати за кожен місяць.
Примітка. У цій статті описано, як об'єднувати файли з папки. Ви також можете об'єднати файли, які зберігаються в SharePoint, сховищі BLOB-об'єктів Azure та Azure Data Lake Storage. Процес схожий.
Не заскладніть цю функцію.
-
Переконайтеся, що всі файли, які потрібно об'єднати, містяться в виділеній папці без зайвих файлів. В іншому разі всі файли в папці та вкладені папки додаються до даних, які потрібно об'єднати.
-
Кожен файл має мати однакову схему з узгодженими заголовками стовпців, типами даних і кількістю стовпців. Стовпці не обов'язково мають збігатися з іменами стовпців.
-
Якщо це можливо, уникайте непов'язаних об'єктів даних для джерел даних, які можуть містити кілька об'єктів даних, наприклад JSON-файл, книгу Excel або базу даних Access.
Кожен із цих файлів має простий шаблон, який містить лише одну таблицю даних у кожному файлі.
-
Виберіть пункт Дані > Отримати дані > з файлу > з папки. Відкриється діалогове вікно Огляд .
-
Знайдіть папку з файлами, які потрібно об'єднати.
-
Список файлів у папці з'явиться в діалоговому вікні> шлях до папки <. Переконайтеся, що всі потрібні файли наведено в списку.
-
Виберіть одну з команд у нижній частині діалогового вікна, наприклад Об'єднати > Об'єднати & Завантажити. У розділі Про всі ці команди описано додаткові команди.
-
Якщо вибрати будь-яку команду Об'єднати , відкриється діалогове вікно Об'єднання файлів . Щоб змінити параметри файлу, виберіть кожен файл у полі Зразок файлу , задайте потрібні параметри "Джерело файлу", "Роздільник" і "Виявлення типів даних ". Також можна встановити або зняти прапорець Пропустити файли з помилками в нижній частині діалогового вікна.
-
Натисніть кнопку OK.
Результат
Power Query автоматично створює запити для об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.
-
Виберіть пункт Дані > Отримати дані > з файлу > з папки. Відкриється діалогове вікно Огляд.
-
Знайдіть папку з файлами, які потрібно об'єднати.
-
Список файлів у папці з'явиться в діалоговому вікні> шлях до папки <. Переконайтеся, що всі потрібні файли наведено в списку.
-
Виберіть одну з команд у нижній частині діалогового вікна, наприклад Об'єднати > Об'єднати & Перетворення. У розділі Про всі ці команди описано додаткові команди.З'явиться Редактор Power Query.
-
Стовпець Значення – це стовпець структурованого списку . Клацніть піктограму Розгорнути
-
Стовпець "Значення" тепер є структурованим стовпцем "Запис ". Клацніть піктограму Розгорнути
-
Залишити виділеними всі стовпці. Можна зняти прапорець Використовувати вихідне ім'я стовпця як префікс . Натисніть кнопку OK.
-
Виділіть усі стовпці, які містять значення даних. На вкладці Основне клацніть стрілку поруч із кнопкою Видалити стовпці, а потім виберіть видалити інші стовпці.
-
Натисніть кнопку Основне > Закрити & Завантажити.
Результат
Power Query автоматично створює запити для об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.
Кожне з цих джерел даних може мати кілька об'єктів для імпорту. Книга Excel може містити кілька аркушів, таблиць Excel або іменованих діапазонів. База даних Access може містити кілька таблиць і запитів.
-
Виберіть пункт Дані > Отримати дані > з файлу > з папки. Відкриється діалогове вікно Огляд.
-
Знайдіть папку з файлами, які потрібно об'єднати.
-
Список файлів у папці з'явиться в діалоговому вікні> шлях до папки <. Переконайтеся, що всі потрібні файли наведено в списку.
-
Виберіть одну з команд у нижній частині діалогового вікна, наприклад Об'єднати > Об'єднати & Завантажити. У розділі Про всі ці команди описано додаткові команди.
-
У діалоговому вікні Об'єднання файлів виконайте такі дії:
-
У полі Зразок файлу виберіть файл, який використовуватиметься як зразок даних для створення запитів. Не можна виділити об'єкт або виділити лише один об'єкт. Але вибрати кілька елементів не можна.
-
Якщо об'єктів багато, скористайтеся полем пошуку , щоб знайти об'єкт або параметри відображення , а також кнопку Оновити , щоб відфільтрувати список.
-
Установіть або зніміть прапорець Пропустити файли з помилками в нижній частині діалогового вікна.
-
-
Натисніть кнопку OK.
Результат
Power Query автоматично створює запит на об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.
Для більшої гнучкості ви можете явно об'єднати файли в Редактор Power Query за допомогою команди Об'єднати файли. Припустімо, що вихідна папка містить кілька типів файлів і вкладених папок, і вам потрібно націлювати певні файли з однаковим типом файлів і схемою, але не іншими. Це може підвищити продуктивність і спростити перетворення.
-
Виберіть елемент Дані > Отримати > данихіз файлу > з папки. Відкриється діалогове вікно Огляд .
-
Знайдіть папку з файлами, які потрібно об'єднати, і натисніть кнопку Відкрити.
-
Список усіх файлів у папці та вкладених папках відображається в діалоговому вікні >шлях до папки< . Переконайтеся, що всі потрібні файли наведено в списку.
-
Натисніть кнопку Перетворення даних у нижній частині екрана. Відкриється Редактор Power Query з усіма файлами в папці та вкладених папках.
-
Щоб вибрати потрібні файли, відфільтруйте стовпці, наприклад Розширення або Шлях до папки.
-
Щоб об'єднати файли в одну таблицю, виділіть стовпець Вміст , який містить кожен двійковий файл (зазвичай це перший стовпець), а потім виберіть Основне > Об'єднати файли. Відкриється діалогове вікно Об'єднання файлів .
-
Power Query аналізує приклад файлу, за замовчуванням перший файл у списку, щоб використовувати правильний з'єднувач і визначати відповідні стовпці.Щоб використати інший файл для прикладу файлу, виберіть його з розкривного списку Зразок файлу .
-
За потреби в нижній частині виберіть пропустити файли з помилками , щоб виключити ці файли з результату.
-
Натисніть кнопку OK.
Результат
Power Query автоматично створює запити для об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.
Є кілька команд, які можна вибрати, і кожна з них має різні призначення.
-
Об'єднання та перетворення даних Щоб поєднати всі файли із запитом, а потім запустити Редактор Power Query, виберіть Об'єднати > Об'єднати та перетворити дані.
-
Об'єднати та завантажити Щоб відкрити діалогове вікно Зразок файлу, створіть запит, а потім завантажте його до аркуша, а потім натисніть кнопку Об'єднати > Об'єднати та завантажити.
-
Об'єднати та завантажити до Щоб відкрити діалогове вікно Зразок файлу, створіть запит, а потім відобразіть діалогове вікно Імпорт , виберіть пункт Об'єднати > Об'єднати та завантажити до.
-
Навантаження Щоб створити запит одним кроком, а потім завантажити його до аркуша, натисніть кнопку Завантажити > Завантажити.
-
Завантажити до Щоб створити запит одним кроком, а потім відкрити діалогове вікно Імпорт , натисніть кнопку Завантажити > Завантажити до.
-
Перетворення данихЩоб створити запит одним кроком, а потім запустити Редактор Power Query, натисніть кнопку Перетворити дані.
Проте ви об'єднуєте файли, в області Запити в групі "Довідкові запити" створюються кілька допоміжних запитів.
-
Power Query створює запит "Зразок файлу" на основі прикладу запиту.
-
У запиті функції "Файл перетворення" використовується запит "Параметр1", щоб указати кожен файл (або двійковий файл) як вхідні дані в запиті "Зразок файлу". Цей запит також створює стовпець Вміст , що містить вміст файлу, і автоматично розширює структурований стовпець Запис, щоб додати дані стовпця до результатів. Запити "Файл перетворення" та "Зразок файлу" зв'язані, тому зміни в запиті "Зразок файлу" відображаються в запиті "Файл перетворення".
-
Запит, який містить остаточні результати, міститься в групі "Інші запити". За замовчуванням він називається на честь папки, з якої імпортовано файли.
Для подальшого дослідження клацніть кожен запит правою кнопкою миші та виберіть Редагувати , щоб перевірити кожен крок запиту та побачити, як запити працюють у узгодженому середовищі.
