Power Query вбудований у версії Windows Office 365, Excel 2016, Excel 2019 і доступний для безкоштовного завантаження у версіях Windows Excel 2010 та Excel 2013. Інструмент призначений для вилучення, перетворення та завантаження даних у Excel із різноманітність джерел. Найкраща частина: Power Query запам'ятовує ваші кроки та відтворюватиме їх, коли ви захочете оновити дані. Це означає, що ви можете очищати дані на 1-й день за 80% звичайного часу, а ви можете очищати дані на 2–4-й дні, просто натискаючи кнопку «Оновити».
Я кажу це про багато нових функцій Excel, але це дійсно найкраща функція для роботи в Excel за 20 років.
На своїх живих семінарах я розповідаю про те, як Power Query було винайдено як милицю для клієнтів служб аналізу SQL Server, які були змушені використовувати Excel для доступу до Power Pivot. Але Power Query постійно покращувався, і кожна людина, яка використовує Excel, мала б витратити час, щоб вивчити Power Query.
Отримайте Power Query
Можливо, у вас вже є Power Query. Він знаходиться в групі Отримати та перетворити на вкладці Дані.
Але якщо ви перебуваєте у програмі Excel 2010 або Excel 2013, перейдіть до Інтернету та виконайте пошук Завантажити Power Query. Команди Power Query з’являться на спеціальній вкладці Power Query на стрічці.
Перший раз очистіть дані в Power Query
Щоб навести вам приклад неймовірності Power Query, скажіть, що ви щодня отримуєте файл, показаний нижче. Стовпець A не заповнено. Чверті переходять навпроти сторінки, а не вниз.
Для початку збережіть цю книгу на жорсткому диску. Помістіть його в передбачуване місце з іменем, яке ви будете використовувати для цього файлу щодня.
В Excel виберіть Отримати дані, З файлу, З робочої книги.
Перейдіть до книги. На панелі попереднього перегляду натисніть Аркуш1. Замість натискання кнопки Завантажити натисніть Редагувати. Тепер ви бачите книгу у дещо іншій сітці - у сітці Power Query.
Тепер вам потрібно виправити всі порожні клітинки в стовпці А. Якщо ви робили це в інтерфейсі користувача Excel, громіздка послідовність команд - це Домашня сторінка, пошук і вибір, перехід до спеціальних, порожні, рівні, стрілка вгору, Ctrl + Enter .
У Power Query виберіть Transform, Fill, Down.
Усі нульові значення замінюються на значення зверху. За допомогою Power Query потрібно три клацання замість семи.
Наступна проблема: квартали йдуть поперек, а не вниз. В Excel це можна виправити за допомогою зведеної таблиці множинного діапазону консолідації. Для цього потрібно 12 кроків і 23+ кліків.
У Power Query виберіть два стовпці, які не є чвертями. Відкрийте спадне меню Unpivot Columns на вкладці Transform і виберіть Unpivot Other Columns, як показано нижче.
Клацніть правою кнопкою миші на щойно створений стовпець Атрибут та перейменуйте його Квартал замість Атрибут. Двадцять плюс кліків у програмі Excel стають п’ятьма клацаннями в Power Query.
Тепер, справедливості заради, не кожен крок очищення коротший у Power Query, ніж у Excel. Видалення стовпця все одно означає правою кнопкою миші натиснути стовпець і вибрати Видалити стовпець. Але, чесно кажучи, історія тут не стосується економії часу на День 1.
Але зачекайте: Power Query пам’ятає всі ваші кроки
Подивіться на праву сторону вікна Power Query. Існує список "Прикладні кроки". Це миттєвий аудиторський слід усіх ваших кроків. Клацніть на будь-яку піктограму шестірні, щоб змінити свій вибір на цьому кроці та здійснити каскад змін у наступних кроках. Клацніть на будь-якому кроці, щоб переглянути, як виглядали дані до цього кроку.
Закінчивши очищення даних, натисніть кнопку Закрити та завантажити, як показано нижче.
Порада
Якщо ваші дані перевищують 1048 576 рядків, ви можете скористатися розкривним списком Закрити та завантажити, щоб завантажити дані безпосередньо в модель даних Power Pivot, яка може вмістити 995 мільйонів рядків, якщо на машині встановлено достатньо пам’яті.
Через кілька секунд ваші перетворені дані з’являться в Excel. Приголомшливо
Виграш: очистіть дані завтра одним клацанням миші
Але знову ж таки, історія Power Query не стосується економії часу на 1-й день. Коли ви вибираєте дані, повернені Power Query, праворуч від Excel з’являється панель Запити та підключення, а на ній є кнопка Refresh. (Тут нам потрібна кнопка Редагувати, але оскільки її немає, вам потрібно клацнути правою кнопкою миші вихідний запит, щоб переглянути або внести зміни до оригінального запиту).
Це приємно чистити дані в день 1. Я люблю робити щось нове. Але коли мій менеджер бачить отриманий звіт і каже “Прекрасно. Ви можете робити це щодня? " Я швидко ненавиджу нудьгу щодо очищення того самого набору даних щодня.
Отже, щоб продемонструвати 400-й день очищення даних, я повністю змінив вихідний файл. Нові продукти, нові клієнти, менша кількість, більше рядків, як показано нижче. Я зберігаю цю нову версію файлу за тим самим шляхом та з тим самим іменем файлу, що і вихідний файл.
Якщо я відкрию книгу запитів і натиснути кнопку Оновити, за кілька секунд Power Query повідомляє 92 рядки замість 68 рядків.
Очищення даних у день 2, день 3, день, 4,… день 400,… День нескінченності тепер займає два клацання.
Цей приклад лише дряпає поверхню Power Query. Якщо ви витратите дві години на книгу, "М для мавпи (даних)" Кена Пулса та Мігеля Ескобара, ви дізнаєтесь про інші особливості, такі як:
- Об’єднання всіх файлів Excel або CSV із папки в єдину сітку Excel
- Перетворення клітинки з яблуком; банан; вишня; кріп; баклажани до п’яти рядків у Excel
- Виконуючи VLOOKUP для книги пошуку, під час введення даних у Power Query
- Перетворення одного запиту на функцію, яку можна застосувати до кожного рядка в Excel
Щоб отримати повний опис Power Query, перегляньте M Is for (Data) Monkey Кена Пулса та Мігеля Ескобара. Наприкінці 2019 року буде доступне перевидане друге видання «Освойте свої дані».
Подяка Мігелю Ескобару, Робу Гарсії, Майку Гірвіну, Рей Хаузеру та Коліну Майклу за номінацію Power Query.
