Microsoft Power Query для Excel – это новая надстройка, которая обеспечивает удобный поиск, трансформацию и обновление данных для информационных работников, дашборд профессионалов и других пользователей.

Power Query дает нам новую вкладку в ленточном интерфейсе Excel, где мы сможем импортировать, преобразовывать и объединять данные из различных источников. В дополнение к стандартным источникам данных, таких как Microsoft Access, SQL и текстовым файлам, Power Query позволяет импортировать из Active Directory, Azure, OData и Hadoop.

Power Query – это бесплатная надстройка для Excel 2010 и Excel 2013, которую можно скачать здесь.

Обратите внимание, что Power Query ранее был известен под кодовым именем “Data Explorer”. Аналогично, Power Map ранее была известна как проект “GeoFlow”.

В сегодняшней статье, я покажу вам несколько примеров, которые можно сделать с помощью надстройки Power Query.

1.      Основы использования Power Query

После установки надстройки, вы увидите новую вкладку Power Query на ленте Excel.

В группе Get External Data в левой части вкладки можно выбрать источники данных. В этом первом примере я выберу From Web и введу интересующий меня сайт.

Через несколько секунд, Excel активирует окно запроса с, найденными на сайте, таблицами. Вы можете щелкнуть по любой из них, чтобы посмотреть, как выглядят данные. Метод импорта данных из интернета мы уже рассматривали в одной из предыдущих статей.

Вы можете удалить данные из таблицы непосредственно в окне запроса. На картинке внизу, я щелкнул правой кнопкой мыши по заголовку столбца и выбрал Use First Row As Header, что означает Использовать первую строку в качестве заголовка.

Обратите внимание на строку формул сверху. Эта строка содержит синтаксис, необходимый для запуска шагов, которые вы выбрали. Выдвижная панель Steps позволяет просматривать или удалять любые действия, которые были применены к запросу. Вы также можете вручную менять формулу, но об этом немного позже.

После того, как запрос будет настроен, можно вернуть данные на рабочий лист Excel. Как вы уже могли догадаться, чтобы обновить данные, щелкните правой кнопкой мыши по таблице и выберите Обновить.

2.      Работа с каналами данных

Инструмент Power Query имеет механизм, поиска данных в интернет. К примеру, вам захотелось посмотреть календарь соревнований зимних Олимпийских игр в Сочи. Для этого вы можете воспользоваться Power Query.

Щелкаем по кнопке Online Search, в появившемся диалоговом окне вводим поисковый запрос Sochi. Результатом работы поисковой машины Excel станет список статей, где встречается слово Sochi. Если мы щелкнем по любому из них, данные появятся на рабочем листе.

3.      Получаем список файлов в папке

Появлялась ли когда-нибудь у вас нужда выгрузить список файлов в папке? У меня да. И для этого я писал отдельный макрос. Ну, это дела давно минувших дней, теперь Power Query позволяет сделать тоже самое в несколько щелчков.

Выбираем From File в группе Get External Data и определяем папку, из которой мы хотим извлечь список.

Excel активирует окно запроса, где будет виден список всех файлов в папке.

К тому же вы можете добавить колонки с дополнительными атрибутами файла, щелкнув по иконке, находящейся справа от заголовка столбца Attributes.

После подтверждения изменений, мы имеем обновляемую таблицу со списком всех файлов в указанной папке. Не уверен, что Power Query был предназначен именно для такой выгрузки, но я считаю эту функцию очень удобной.

4.      Консолидация данных, или как создать одну таблицу из нескольких Excel файлов

Как я уже упоминал ранее, каждое действие в Power Query создает некую формулу, которая описывает новый шаг в процессе извлечения данных.

Чтобы открыть окно с формулами запроса, щелкните по иконке в виде листочка в окне запроса:

Изначально синтаксис выглядит безнадежно непроницаемым. Но немного поигравшись с Power Query, вы поймете, как он работает. Ключ в том, что необходимо сделать какие-либо действия в запросе, а затем посмотреть, как изменится код. Через некоторое время вы сможете создавать собственные запросы для выполнения сложных действий.

Вы также можете посетить страницу формул Power Query для большего понимания.

Для примера предположим, что мне необходимо получить данные с двух файлов Excel и поместить их в одну таблицу. Структура файлов одинакова (в каждом одинаковые колонки).

Банальный копипэйст меня не устраивает, так как эту операцию необходимо выполнять еженедельно. Требуется что-то более автоматизированное. Нам поможет Power Query, с помощью которого мы сможем написать запрос для получения данных.

Для начала необходимо включить расширенные возможности редактирования запросов, во вкладке Power Query в группе Machine Settings щелкаем по кнопке Options -> Enable Advanced Query Editing.

Теперь мы готовы писать запрос. В группе Get External Data выбираем From Other Sourses -> Blank Query.

Данная опция позволяет мне напрямую ввести необходимый код. В данном случае я скомбинировал таблицы обоих файлов, при этом первую строку использовал как заголовки.

После подтверждения всех изменений, Excel выдаст консолидированную таблицу с данными обоих файлов. И самое замечательное во всем этом – это возможность обновления в любое время.

Важная мысль: если вы отправите файл содержащий запрос Power Query кому-то у кого он не был установлен, этот пользователь увидит данные таблицы, но не сможет их обновить.

Вам также могут быть интересны следующие статьи

• Пошаговая инструкция по созданию надстройки Excel для ленты
• Надстройка Inquire в excel 2013 — сравнение, анализ и связь файлов Excel
• Приложения для Excel — Apps for Excel: установка, использование, удаление
• Поиск решений в Excel — пример использования сервиса поиск решений в Excel
• Создание простейшего дашборда с помощью надстройки PowerView в Excel
• Визуализация географических данных с помощью надстройки GeoFlow