Рейтинг: 5/5
Power Query розпізнав і розділив дані по стовпцях. Також автоматично виконані кроки:
• Перший рядок підвищена в заголовки
• Змінено тип для кожного стовпця
Якщо обробки не потрібно, то результат можна вивантажувати в книгу Ексель Головна - Закрити - Закрити і завантажити.
На новому аркуші створюється Таблиця Excel.
Вискочить попередження про те, що таблиця буде видалена. Натискаємо Продовжити. Таблиця з Excel пішла, запит залишився у вигляді з'єднання.
Тепер у нас два запити, з яких потрібно зробити загальний, який би два файли.
У Power Query є два типи об'єднання запитів: Додавання (Append) і Об'єднання (Merge). Нас цікавить додавання, тому що таблиці повинні бути складені разом (одна під інший). Об'єднання потрібно для злиття запитів по ключовому полю, але про це іншим разом.
Зараз заходимо Головна - Завантажити і перетворити - Створити запит - Об'єднати запити - Додати.
Далі потрібно вибрати додаються запити.
Слід розуміти дві речі.
• Додавати можна тільки запити (а не Таблиці Excel)
• Запит в верхньому полі буде першим зверху
Додати можна було б і більше запитів, вибравши в меню вгорі Три таблиці або більше. але у нас тільки два. Натиском ОК. Створюється об'єднаний запит під назвою Append1.
Змінимо назву на Звіт про продажі і вивантажимо дані в Excel (Головна - Закрити - Закрити і завантажити). Разом отримаємо три запиту.
Перші два служать джерелом для об'єднаного, який вивантажено на лист Excel. Створимо за цими даними зведену таблицю.
Натискаємо ОК і знову вивантажуємо оновлені дані в Excel (Головна - Закрити - Закрити і завантажити). Тепер в Таблиці Excel знаходяться дані за три місяці. Потрібно тільки оновити зведену таблицю.
Для імпорту нових файлів потрібно буде повторити всі дії:
• створення нового запиту
• додавання його в який об'єднує запит Звіт про продажі
• вивантаження в Excel
• оновлення зведеної таблиці.
Незважаючи на те, що таким чином можна консолідувати дані з багатьох файлів, був би здорово, якби вони самі додавалися до зведеного запит. Так теж можна.
Імпорт з папки
Джерелом в Power Query можуть бути не тільки окремі файли, але і ціла папка з усім її вмістом. Далі справа наступної техніки.
Виглядає підозріло, тому що замість даних щось незрозуміле. Дійсно, зараз видно тільки файли, що містяться в папці, і інформація про них: назва, розширення, час створення, зміни і т.д. Переходимо до вилучення даних з цих файлів.
Наступний крок не є обов'язковим, але він дозволить уникнути деяких можливих проблем в майбутньому. Потрібно розуміти, що весь вміст зазначеної папки буде вилучено з допомогою запиту Power Query. І якщо туди потрапить який-небудь, наприклад, файл Excel, то запит «поламається» і видасть помилку. Тому досвідчені користувачі створюють «захист», щоб файли з іншим розширенням відфільтровувались.
Ідея в тому, щоб в стовпці Extension поставити фільтр на розширення .csv. Щоб випадково не відфільтрувати файли .CSV, зробимо все літери для стовпця Extension маленькими. Для цього виділяємо стовпець, далі через праву кнопку миші Перетворення - у нижньому. Переходимо до установки фільтра. Якщо в випадаючому списку фільтра поставити перемикач на значення .csv, то у нас нічого не вийде, тому що при єдиному типі файлів буде автоматично виділено пункт Вибрати все. Тому вибираємо Текстові фільтри - Так само ... і вказуємо .csv (обов'язково з точкою попереду).
Тепер випадкове додавання в зазначену папку файлів Excel не вплине на роботу запиту. Цей крок, повторюся, необов'язковий, але краще прислухатися до поради досвідчених користувачів.
Приступимо до вилучення даних. Вміст файлів приховано в колонці Content за значенням Binary.
Перед тим, як розгорнути вміст цього стовпця, позбудемося зайвої інформації. Виділяємо стовпець Content і через праву кнопку миші вибираємо Видалити інші стовпці.
Настав час сеансу магії з викриттям. У верхньому правому куті знаходиться кнопка з двома стрілками, спрямованими вниз.
Це кнопка завантаження двійкового (бінарного) файлу. Тиснемо. І о диво! Вміст всіх трьох файлів один за іншим вивантажується в єдину таблицю.
Однак на цей раз буде потрібно вручну внести деякі корективи.
• Вилучимо останній крок Змінений тип
• Перетворення - Використовувати перший рядок в якості заголовків
• Правою кнопкою миші по полю Дата - Тип зміни - Дата
• Утримуючи Shift, виділяємо два стовпці Найменування і Менеджер. потім через праву кнопку миші Тип зміни - Текст
• Через Shift виділяємо інші стовпці Ціна. Вартість. Комісія - права клавіші миші - Тип зміни - Десяткове число
• Правою кнопкою миші по полю Дата - Видалити помилки
• Головна - Закрити - Закрити і завантажити
Таким чином, ми отримуємо таблицю з єдиним заголовком, зроблену з трьох файлів. На її основі створимо зведену таблицю.
Зведена таблиця побудована за 116 рядків. Таким же чином можна було б об'єднати і 10 файлів з сотнями тисяч рядків.
Додавання в запит нових файлів і оновлення зведеної таблиці
І ось тут настає момент істини. Все що потрібно, це закинути нові файли в вказану папку і на стрічці у вкладці Дані натиснути Оновити все. Перше натискання оновить всі запити, друге - зведені таблиці.
Варто тільки пам'ятати, що для об'єднання подібним чином таблиць з різних текстових файлів необхідно повний збіг заголовків, інакше вони автоматично розлучаться з різних стовпчиках.
Для самостійної тренування можна скачати архів файлів .csv і готове рішення в Excel (воно буде працювати, якщо на першому кроці запиту (подвійний клік миші) поправити шлях до файлу).