- Power Query дозволяє об’єднувати кілька файлів із локальної або хмарної папки, якщо вони мають однакову схему даних.
- Використання файлу-зразка спрощує застосування масових перетворень, таких як видалення верхніх рядків або зміна заголовків.
- Існують обмеження, коли стовпці не повністю збігаються, що вимагає ручного очищення або використання скриптів VBA для складних випадків.
Уявіть, що у вас є купа щомісячних або щотижневих звітів, розкиданих по папках, і замість того, щоб витрачати ранок на шалене копіювання та вставку даних, ви можете об’єднати все в одну електронну таблицю Excel лише кількома клацаннями миші. Саме тут і з’являється Power Query – інструмент, який по суті є правою рукою кожного, хто працює з даними, дозволяючи нам без зусиль консолідувати файли з однаковою структурою.
Найкрутіше в цій системі те, що вона не обмежує вас локальною папкою на вашому ПК; ви можете отримувати дані з сайтів SharePoint, Azure Data Lake або Azure blobs . Ключем до безперебійної роботи є те, що файли мають однакову схему, тобто стовпці збігаються, хоча Power Query достатньо розумний, щоб допомогти очистити дані, якщо вони містять будь-який шум або непотрібні рядки.
Покроковий процес комбінування
Під час підключення до джерела даних перше, що ви побачите, це вигляд файлової системи. Тут найважливішим стовпцем є «Вміст», який містить двійкове представлення кожного документа. Якщо ви натиснете на одне з цих двійкових значень, програма спробує визначити, чи це файл CSV, JSON чи Excel, щоб відкрити його. Щоб об’єднати весь пакет, у вас є два варіанти: натиснути кнопку «Об’єднати» безпосередньо або, якщо ви більш ретельні, вибрати «Трансформувати дані», щоб відфільтрувати список файлів (наприклад, щоб ігнорувати певні папки), перш ніж продовжити об’єднання.
Після відкриття діалогового вікна злиття Power Query використовує файл-зразок для аналізу структури . За замовчуванням вибирається перший файл у списку, але ви можете змінити його, якщо є інший, який краще представляє набір даних. Тут ви можете вибрати опцію пропускання файлів з помилками , що є рятівником, коли звіт пошкоджений або порожній, запобігаючи зупинці всього процесу.
Управління виходом та очищення даних
Після прийняття об’єднання система виконує певну технічну магію: створює зразок запиту та параметризовану функцію. Це означає, що будь-які зміни, внесені до файлу зразка, будуть автоматично репліковані на всі інші файли в папці. Наприклад, якщо ваші CSV-файли мають чотири рядки непотрібних заголовків перед фактичними даними, ви можете перейти до запиту на перетворення та скористатися опцією видалення верхніх рядків , щоб позбутися цього безладу.
Після очищення рядків важливо застосувати функцію « Використовувати перший рядок як заголовки» . Майте на увазі, що коли ви це робите, Power Query зазвичай додає крок автоматичного перетворення типів даних. Якщо після повернення до основного запиту ви бачите помилку про те, що стовпець «Стовпець1» не знайдено, не хвилюйтеся; це нормально, оскільки ви перейменували стовпці в прикладі , і наступний крок більше не розпізнає старі назви. Просто видаліть цей помилковий крок з області «Застосовані кроки», і все повернеться до норми.
Поширені проблеми та передові альтернативи
Іноді життя не таке просте, і ми стикаємося з файлами, які не є ідентичними. Наприклад, якщо у вас є звіти ServiceNow, де один заголовок називається «resolved_on», а інший — «closed_on», Power Query може створювати проблеми, оскільки вимагає узгодженої структури . Якщо файл-зразок не зовсім відповідає решті, інструмент може пропустити інформацію з невідповідних файлів або видавати помилки імпорту.
Для випадків, коли структура повністю хаотична або обсяг даних величезний (перевищує один мільйон рядків Excel), існують інші варіанти. Деякі користувачі обирають скрипти VBA з використанням інтерфейсів CSV , які дозволяють об'єднувати файли без повторення заголовків, незалежно від того, чи відрізняється роздільник полів або записів від файлу до файлу. Хоча оболонка Windows надзвичайно швидка, вона часто дає збої під час обробки вбудованих розривів рядків, тоді як надійне рішення VBA не перевантажує оперативну пам'ять і може легко обробляти мільйони записів.
Щоб завершити процес, після об’єднання даних у Power Query не забудьте призначити правильний тип даних кожному стовпцю (Дата для дат, Валюта для доходу тощо) перед завантаженням остаточної таблиці. Ви можете перевірити правильність усіх налаштувань, відфільтрувавши стовпець «Назва джерела», щоб переконатися, що всі файли в папці успішно оброблено.
Можливість автоматизувати об'єднання кількох CSV-файлів дозволяє перейти від виснажливої ручної роботи до професійного робочого процесу, використовуючи гнучкість Power Query для очищення даних або вдаючись до VBA, коли структури несумісні або обсяги перевищують межі електронних таблиць.