Power Query встроен в версии Office 365, Excel 2016, Excel 2019 для Windows и доступен для бесплатной загрузки в версиях Excel 2010 и Excel 2013 для Windows. Инструмент предназначен для извлечения, преобразования и загрузки данных в Excel из разнообразие источников. Лучшая часть: Power Query запоминает ваши шаги и воспроизводит их, когда вы хотите обновить данные. Это означает, что вы можете очистить данные в первый день за 80% обычного времени, а вы можете очистить данные в дни со 2 по 400, просто нажав «Обновить».
Я говорю это о многих новых функциях Excel, но это действительно лучшая функция, которая появилась в Excel за 20 лет.
На своих семинарах я рассказываю историю о том, как Power Query был изобретен как костыль для клиентов служб SQL Server Analysis Services, которые были вынуждены использовать 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, предположим, что вы получаете файл, показанный ниже, каждый день. Столбец А не заполнен. Четверти идут поперек, а не вниз по странице.
Для начала сохраните книгу на жесткий диск. Поместите его в предсказуемое место с именем, которое вы будете использовать для этого файла каждый день.
В Excel выберите «Получить данные», «Из файла», «Из книги».
Перейдите к книге. На панели предварительного просмотра щелкните Sheet1. Вместо того, чтобы нажимать «Загрузить», нажмите «Изменить». Теперь вы видите книгу в немного другой сетке - сетке Power Query.
Теперь вам нужно исправить все пустые ячейки в столбце A. Если вы должны были сделать это в пользовательском интерфейсе Excel, громоздкая последовательность команд: «Домой», «Найти и выделить», «Перейти к специальному», «Пробелы», «Равно», «Стрелка вверх», «Ctrl + Enter». .
В Power Query выберите Transform, Fill, Down.
Все нулевые значения заменяются значением сверху. С Power Query для этого требуется три щелчка мышью вместо семи.
Следующая проблема: кварталы идут поперек, а не вниз. В Excel это можно исправить с помощью сводной таблицы с несколькими диапазонами консолидации. Это требует 12 шагов и 23+ кликов.
В Power Query выберите два столбца, которые не являются четвертями. Откройте раскрывающийся список Unpivot Columns на вкладке Transform и выберите Unpivot Other Columns, как показано ниже.
Щелкните правой кнопкой мыши только что созданный столбец Attribute и переименуйте его в Quarter вместо Attribute. Двадцать с лишним щелчков в Excel превращаются в пять щелчков в Power Query.
Честно говоря, не каждый шаг очистки в Power Query короче, чем в Excel. Удаление столбца по-прежнему означает щелчок правой кнопкой мыши по столбцу и выбор «Удалить столбец». Но, честно говоря, история здесь не об экономии времени в первый день.
Но подождите: Power Query запоминает все ваши шаги
Посмотрите на правую часть окна Power Query. Есть список под названием «Примененные шаги». Это мгновенный контрольный журнал всех ваших шагов. Щелкните любой значок шестеренки, чтобы изменить свой выбор на этом шаге и каскадировать изменения на следующих шагах. Щелкните любой шаг, чтобы увидеть, как данные выглядели до этого шага.
Когда вы закончите очистку данных, нажмите «Закрыть и загрузить», как показано ниже.
Подсказка
Если ваши данные содержат более 1048576 строк, вы можете использовать раскрывающееся меню «Закрыть и загрузить», чтобы загрузить данные непосредственно в модель данных Power Pivot, которая может вместить 995 миллионов строк, если у вас достаточно памяти, установленной на компьютере.
Через несколько секунд ваши преобразованные данные появятся в Excel. Потрясающие.
Результат: чистые данные завтра одним щелчком мыши
Но опять же, история Power Query не об экономии времени в первый день. Когда вы выбираете данные, возвращаемые Power Query, в правой части Excel появляется панель «Запросы и подключения», на которой находится кнопка «Обновить». (Здесь нам нужна кнопка «Изменить», но поскольку ее нет, необходимо щелкнуть исходный запрос правой кнопкой мыши, чтобы просмотреть или внести изменения в исходный запрос).
Чистить данные в первый день - это весело. Я люблю делать что-то новое. Но когда мой менеджер видит получившийся отчет и говорит: «Красиво. Ты можешь делать это каждый день? » Я быстро начинаю ненавидеть утомительную очистку одного и того же набора данных каждый день.
Итак, чтобы продемонстрировать 400-й день очистки данных, я полностью изменил исходный файл. Новые продукты, новые клиенты, меньшее количество, больше строк, как показано ниже. Я сохраняю эту новую версию файла по тому же пути и с тем же именем, что и исходный файл.
Если я открою книгу запросов и нажму «Обновить», через несколько секунд Power Query отобразит 92 строки вместо 68.
Очистка данных на День 2, День 3, День, 4,… День 400,… День бесконечности теперь требует двух щелчков мышью.
Этот пример лишь поверхностно затрагивает Power Query. Если вы проведете два часа с книгой, M означает (Data) Monkey Кена Пульса и Мигеля Эскобара, вы узнаете о других функциях, таких как:
- Объединение всех файлов Excel или CSV из папки в единую сетку Excel
- Преобразование ячейки с помощью Apple; Banana; Cherry; Dill; Eggplant в пять строк в Excel
- Выполнение ВПР для книги поиска при переносе данных в Power Query
- Создание единого запроса в функции, которая может применяться к каждой строке в Excel
Полное описание Power Query можно найти в статье M Is for (Data) Monkey Кена Пульса и Мигеля Эскобара. К концу 2019 года будет доступно второе издание под названием Master Your Data.
Спасибо Мигелю Эскобару, Робу Гарсиа, Майку Гирвину, Рэю Хаузеру и Колину Майклу за номинирование Power Query.
