Power Query в Excel: імпорт, очищення, об’єднання та оновлення даних
By Mirko Peters · · Updated
Power Query в Excel імпортує та очищує дані без формул. Дізнайтеся, де його знайти, як працюють From Table/Range та Applied Steps, у чому різниця між merge і append, а також як завантажувати й оновлювати результат.
Automatically translated with AI from the original post. Read the English original
Power Query в Excel – це інструмент на вкладці Дані, у групі Отримати & перетворити дані, який підключається до даних, очищує й перероблює їх і завантажує результат на аркуш. Кроки, які ви виконуєте, записуються і повторюються при кожному оновленні, тож щомісячне очищення перетворюється на одне клацання. Ця стаття для користувачів Excel, які щотижня копіюють, вставляють і виправляють одні й ті самі експорти та хочуть цьому покласти край. Тут ви дізнаєтеся, з чого почати, як очистити таблицю, як виконати append і merge, як завантажувати й оновлювати дані, а також які є обмеження на Mac і у вебі.
Головне
- Power Query в Excel розташований на вкладці «Дані» в групі «Отримати та перетворити дані». Він підключається до даних, перетворює їх і завантажує на аркуш або в модель даних.
- Кожна ваша зміна записується як крок в Applied Steps. Вихідне джерело не змінюється, а кроки повторюються під час кожного оновлення.
- Append додає рядки з таблиць з однаковими стовпцями. Merge з’єднує дві таблиці за збіжними ключовими стовпцями, і ці стовпці мають мати однаковий тип даних.
- Щоб повторно виконати запити, використовуйте «Дані» > «Оновити все». У властивостях підключення можна налаштувати оновлення з певним інтервалом або під час відкриття файлу.
- Excel для Mac та Excel для вебу підтримують частину можливостей Power Query. Сторінки Microsoft відрізняються щодо того, що саме кожна версія дозволяє створювати, тож перевірте свою збірку.
Microsoft описує чотири етапи: підключення до даних, їх перетворення, поєднання з іншими джерелами та завантаження. Вихідне джерело весь час залишається без змін. Саме це робить експерименти безпечними: ви не можете зіпсувати експорт, з якого почали.
Що таке Power Query і де його знайти
Power Query – це рушій підготовки даних із графічним редактором. Згідно зі сторінкою Microsoft про те, що таке Power Query, він виконує витягування, перетворення та завантаження даних, не має власного сховища й залишає призначення результату за продуктом-хостом. Excel – один із хостів. Power BI Desktop – інший, і обидва використовують спільний редактор. Конектори та призначення можуть відрізнятися залежно від хосту, тож не варто вважати, що запит, який ви бачили в одному продукті, в іншому виглядатиме так само.
Де саме він розміщений, залежить від версії Excel – згідно з оглядом Power Query в Excel від Microsoft:
- Excel 2016, 2019 та Microsoft 365 для Windows. Вбудовано. Перейдіть на вкладку Дані і подивіться на групу Отримати та перетворити дані.
- Excel 2010 та 2013 для Windows. Power Query був безкоштовною надбудовою з власною вкладкою стрічки. Microsoft називає її застарілою, хоча вона може ще працювати.
- Excel для Mac. Підтримує оновлення з низки джерел. В Excel 2016 або 2019 для Mac його немає.
- Excel для вебу. Підтримується частково. Детальніше нижче.
У Windows Microsoft також вказує як вимоги .NET Framework 4.7.2 або новішу версію та WebView2 Runtime для веб-конектора. Якщо на робочому ПК цих команд немає, спитайте ІТ-відділ, чи збірка актуальна, перш ніж вважати, що функцію прибрали.
Як використовувати Power Query в Excel: ваш перший запит
Найлегше почати з даних, які вже є в книзі.
- Клацніть будь-яку клітинку у вашій таблиці Excel. Якщо дані є звичайним діапазоном, спершу натисніть Ctrl+T, щоб перетворити їх на таблицю.
- Перейдіть до Дані > Отримати й перетворити дані > Із таблиці/діапазону.
- Відкриється Power Query Editor із попереднім переглядом ваших даних.
- Внесіть зміни (див. наступний розділ).
- Оберіть Home > Close & Load.
На сторінці Microsoft про імпорт із таблиці Excel сказано, що результат за замовчуванням потрапляє на новий аркуш, а вихідний аркуш не змінюється. Використовуйте Load To, якщо потрібне інше місце призначення. Перейменуйте новий аркуш, щоб його ніхто не сплутав із джерелом.
Щоб під'єднатися до іншого джерела, скористайтеся Data > Get Data і оберіть джерело: файл, базу даних, онлайн-сервіс або веб. Після вибору ви побачите попередній перегляд і вибір між Load (одразу в Excel) та Transform Data (спершу відкрити редактор). Моя порада: майже завжди обирайте Transform Data, навіть коли здається, що дані чисті. У редакторі задаються типи стовпців, а неправильні типи викликають більшість помилок оновлення згодом.
Якщо хочете почати з порожнього запиту, та сама сторінка пропонує Data > Get Data > From Other Sources > Blank Query або Launch Power Query Editor у меню Get Data.
Редактор простими словами
- Попередній перегляд даних показує вашу таблицю та оновлюється після кожної зміни.
- Панель Query Settings містить список APPLIED STEPS. Кожна ваша дія додає крок, наприклад Source або Changed Type. Можна вибрати крок, щоб побачити дані на цьому етапі, змінити крок або видалити його.
- Панель Queries містить усі запити в книзі. Відкрийте її стрілкою поруч із попереднім переглядом.
- Advanced Editor дозволяє переглядати й редагувати базовий скрипт мовою Power Query M. Microsoft зазначає, що кожен крок, створений в інтерфейсі, записується мовою M. Щоб отримати користь від інструмента, торкатися її не обов'язково.
Очищення та перетворення даних кроками
Типові завдання очищення в редакторі: фільтрація рядків, видалення стовпців, зміна типів даних, розділення стовпця, заміна значень. Редактор записує кожне з них як крок. Команди розміщені на стрічці редактора та в меню, яке з'являється після клацання правою кнопкою миші на заголовку стовпця. Які саме команди доступні, залежить від типу стовпця, тож перш ніж шукати на стрічці, перевірте меню заголовка.
Порядок дій, який працює для більшості експортів:
- Спершу задайте типи. Перевірте значок типу поруч із кожним заголовком стовпця. Дати, числа й текст поводяться по-різному в наступних кроках і при об'єднаннях.
- Приберіть зайве. Менше стовпців означає менший і швидший результат та менше несподіванок, коли джерело змінюється.
- Відфільтруйте рядки. Скористайтеся стрілкою в заголовку стовпця, щоб прибрати порожні значення або небажані дані.
- Виправте текст. Замініть друкарські помилки та старі коди, обріжте пробіли або розділіть стовпець, який містить дві частини інформації.
- Перейменуйте запит на панелі Query Settings, щоб легко знайти його пізніше.
Оскільки Applied Steps є списком, ви можете виправити попередню помилку без початку з нуля. Якщо крок видає помилку після зміни джерела, перегляньте перший крок із помилкою. Найчастіше він вказує на стовпець, чия назва або тип змінилися.
Одне застереження щодо перейменованих стовпців: сторінка Microsoft про оновлення зазначає, що нові стовпці джерела з'являються в Power Query лише після перегляду кроку Source, а жовта смуга з повідомленням, що попередній перегляд може бути застарілим, означає, що слід оновити локальний кеш. Якщо новий стовпець не з'являється, спершу перевірте саме ці два моменти.
Поєднання даних: append і merge
Ці дві команди виконують різні завдання, і люди часто їх плутають.
Append додає рядки до іншої таблиці. Використовуйте його, коли маєте багато таблиць однакового типу, наприклад по одному експорту за кожен місяць. Microsoft описує append як додавання рядків одного запиту до іншого. Є два варіанти: inline append працює з наявним запитом, а intermediate append щоразу створює новий запит.
Merge з'єднує дві таблиці поруч за збігами значень, як пошук у довіднику. На сторінці Microsoft про об'єднання запитів описано кроки:
- Переконайтеся, що маєте щонайменше два запити зі стовпцем для зіставлення. Стовпці для зіставлення повинні мати однаковий тип даних, наприклад Text або Number.
- У редакторі перейдіть до Home > Merge Queries. Результат стає новим кроком у поточному запиті. Якщо потрібен окремий запит, натисніть стрілку поруч із Merge Queries і виберіть Merge Queries as New.
- Виберіть основну таблицю та її ключовий стовпець, а потім пов'язану таблицю та її ключовий стовпець. Для кількох ключових стовпців вибирайте їх в одному й тому самому порядку в обох таблицях.
- Перевірте кількість збігів, яка відображається в діалоговому вікні. Якщо вона менша, ніж ви очікуєте, ключові стовпці, ймовірно, відрізняються написанням, пробілами або типом.
- Виберіть Join Kind (нижче) і натисніть OK.
- З'являється новий стовпець. Натисніть піктограму Expand поруч із його заголовком і позначте пов'язані стовпці, які хочете додати.
Види з'єднання за описом Microsoft:
- Left outer зберігає всі рядки основної таблиці та збіги з пов'язаної таблиці.
- Right outer зберігає всі рядки пов'язаної таблиці та збіги з основної таблиці.
- Full outer зберігає всі рядки з обох таблиць.
- Inner зберігає лише рядки, які мають збіги в обох таблицях.
- Left anti зберігає рядки основної таблиці, для яких немає збігів. Right anti зберігає рядки пов'язаної таблиці, для яких немає збігів.
- Cross поєднує кожен рядок основної таблиці з кожним рядком пов'язаної таблиці.
Порядок таблиць має значення, адже left і right залежать від того, яку таблицю ви вибрали першою. Прапорець Use fuzzy matching to perform the merge зіставляє приблизні значення, і, за словами Microsoft, він працює лише для текстових стовпців. Якщо раніше ви користувалися пошуком, найближчим аналогом буде merge з left outer join, а left anti join — швидкий спосіб побачити рядки без збігів, наприклад замовлення без запису про клієнта.
Microsoft також згадує рівні конфіденційності. Power Query класифікує кожне джерело як Public, Organizational або Private і використовує ці рівні, щоб запобігти випадковому поєднанню даних, яке могло б розкрити приватну інформацію іншому джерелу. Якщо merge запитує про рівні конфіденційності, уважно прочитайте повідомлення, перш ніж натиснути «далі».
Завантажте запит і оновіть його
Закрийте редактор через Home > Close & Load. На сторінці Microsoft про створення, завантаження та редагування запиту пояснено такі місця призначення:
- Close & Load надсилає результат на аркуш.
- Close & Load To дозволяє вибрати інше місце призначення, наприклад Data Model. У діалоговому вікні Import Data позначте Add this data to the Data Model, якщо плануєте будувати зведені таблиці за кількома таблицями.
- Якщо Load To вимкнено, скористайтеся Close & Load, потім перейдіть до Data > Queries & Connections, клацніть запит правою кнопкою миші на вкладці Queries і виберіть Load To.
Щоб змінити параметри за замовчуванням, перейдіть до File > Options and settings > Query Options > Data Load. Можна використати стандартні параметри завантаження або задати власні значення за замовчуванням для завантаження на аркуш чи в Data Model.
Щоб пізніше відредагувати запит, виберіть комірку з його даними та перейдіть до Query > Edit, або клацніть запит правою кнопкою миші в Queries & Connections і виберіть Edit.
Оновлення
Оновлення повторно виконує всі кроки для поточного джерела. Способи, за сторінкою Microsoft про оновлення:
- Data > Refresh All оновлює всі дані в книзі (Ctrl+Alt+F5). Поруч із цією кнопкою стрілка відкриває Refresh для вибраного елемента (Alt+F5). Клавіша Esc зупиняє оновлення.
- Щоб налаштувати автоматичне оновлення, перейдіть до Data > Queries & Connections, відкрийте вкладку Connections, клацніть правою кнопкою миші на потрібному підключенні та виберіть Properties. На вкладці Usage можна задати Refresh every (у хвилинах) і Refresh data when opening the file.
- Enable background refresh на цій самій вкладці дає змогу продовжувати роботу під час виконання запиту. Ця опція недоступна для запитів OLAP та для підключень, які живлять модель даних.
Збережені паролі не шифруються, тож Microsoft радить їх не зберігати. Зніміть прапорець Save password на вкладці Definition, і Excel запитуватиме пароль один раз за сесію.
Якщо ви надсилаєте книгу комусь іншому, цій особі може знадобитися доступ до джерела. Запит, який читає файл на вашому диску або сайт, до якого вона не має доступу, на її комп'ютері завершиться помилкою. Розмістіть джерело даних у спільному місці або надсилайте завантажені результати як значення.
Power Query на Mac і в Excel для веба
Обидві платформи підтримують частину цього інструмента, але сторінки Microsoft повністю не збігаються.
Excel для вебу. На сторінці Microsoft сказано, що можна імпортувати дані з таких джерел, як книга Excel, Text/CSV, XML, JSON, SQL Server, список SharePoint Online та OData, а також створити порожню таблицю або порожній запит. Можна відкрити Power Query Editor (Data > Get Data > Launch Power Query Editor), завантажити дані командою Close & Load і оновити їх через Data > Refresh All. Перегляд і оновлення запитів доступні всім передплатникам Microsoft 365, тоді як для розширених можливостей потрібен план для бізнесу або корпоративний план (E3 і вище). Деякі функції потребують увімкнених сторонніх файлів cookie у браузері.
Excel для Mac. Одна сторінка Microsoft пояснює, що почати слід із Data > Get Data > Get Data (Power Query), що для цього потрібна Microsoft 365 та версія 16.69 або новіша, а Transform Data відкриває редактор. На цій самій сторінці окремий розділ стверджує, що створення запитів у редакторі недоступне в Excel для Mac, і описує написання запитів на VBA у Windows та імпорт модуля на Mac. Ці два твердження я не можу узгодити, тому перед тим, як будувати на цьому процес, перевірте все на власному Mac. Імпорт із SQL Server описано як доступний лише в бета-версії для учасників Insiders.
Коли Power Query недостатньо
Скажу прямо: Power Query призначений для підготовки даних, а не для їх зберігання. Microsoft пише, що куди потраплять результати, визначає програма-хост, а в Excel це аркуш або модель даних. Аркуш має фіксовану межу рядків, тож дуже великий результат запиту на аркуш не поміститься. Завантажуйте його в модель даних або переносьте обробку в базу даних чи окремий конвеєр.
Інші обмеження, які варто знати:
- Запити зчитують джерело під час виконання. Вони не передають зміни в книгу в реальному часі.
- Спільна робота ускладнена. Двом людям редагувати один і той самий запит у спільній книзі не варто. Домовтеся про одного власника та давайте запитам зрозумілі назви.
- Якщо ви працюєте з регульованими даними, пам'ятайте, що книгу легко скопіювати. Дотримуйтеся правил вашої організації щодо місць зберігання таких файлів.
Типові помилки
- Пропуск типів даних, а потім дивування, чому об'єднання нічого не знаходить.
- Об'єднання за стовпцями різних типів або з кінцевими пробілами.
- Використання об'єднання (merge), коли потрібне додавання (append), або навпаки.
- Завантаження всього на аркуш, коли краще підходить запит лише для підключення або модель даних.
- Збереження пароля до джерела в підключенні.
- Забута оновлення перед надсиланням звіту.
- Переміщення файлу джерела і подальше нерозуміння, чому запит завершується помилкою.
Пов'язані теми для вивчення
- Видалення дублікатів у редакторі. Power Query може видалити дублікати в таблиці як окремий крок, який можна повторювати, на відміну від одноразової команди на стрічці.
- Перетворення стовпців на рядки (unpivot). Перетворіть стовпці з місяцями на рядки перед створенням зведеної таблиці. Microsoft згадує Power Query саме для цього.
- Параметри. У Microsoft є сторінка про параметризовані запити, які дають змогу повторно використовувати запит з різними вхідними даними.
- Мова M. Дізнайтеся, як читати скрипт у розширеному редакторі, почавши зі сторінки Power Query M.
- Power BI. Дізнайтеся, як той самий редактор працює в Power BI Desktop, коли ваші дані переростають Excel.
Наступний крок
Оберіть один експорт, який ви щотижня чистите вручну. Перетворіть його на таблицю, проженіть через «З таблиці/діапазону» (From Table/Range), задайте типи стовпців і завантажте на новий аркуш. Наступного тижня вставте новий експорт у таблицю та натисніть «Оновити все» (Refresh All). Ось так Power Query в Excel і виконує свою роботу.
Джерела
- Про Power Query в Excel (підтримка Microsoft)
- Що таке Power Query? (Microsoft Learn)
- Імпорт із таблиці Excel (підтримка Microsoft)
- Створення, завантаження й редагування запиту в Excel (Power Query) (підтримка Microsoft)
- Додавання запитів (Power Query) (підтримка Microsoft)
- Об’єднання запитів (Power Query) (підтримка Microsoft)
- Оновлення зовнішнього підключення до даних в Excel (підтримка Microsoft)
- Використання Power Query в Excel для вебу (підтримка Microsoft)
- Імпорт і формування даних в Excel для Mac (Power Query) (підтримка Microsoft)