Microsoft Power Query — новый инструмент для самостоятельного бизнес-анализа (BI), с помощью которого конечные пользователи могут найти и подготовить данные для анализа. BI — многоэтапный процесс (включает сбор, хранение, моделирование, анализ и обмен данными), в результате которого неструктурированные данные превращаются в полезную информацию. Power Query, ориентированный непосредственно на этап сбора данных, обеспечивает публикацию и повторное использование запросов. Несмотря на возможности подключения и сбора информации из разнообразных источников, уже заложенные в Excel (и Power Pivot), функции преобразования данных Power Query превосходят Excel и выражения анализа данных DAX. В результате появляется сценарий, более удобный для обслуживания, чем сложные формулы и макрокоманды Excel.
Power Query, выпускаемый как надстройка для Microsoft Excel 2013 и Excel 2010, упрощает поиск, объединение и уточнение данных из общедоступных и частных источников. Далее в статье будет показан пример использования Power Query для загрузки данных из Интернета. Желающие подробно отслеживать выполнение могут загрузить Power Query c сайта Power BI for Office 365 (http://office.microsoft.com/en-us/excel/power-bi-download-add-in-FX104087144.aspx). Обратите внимание, что не во всех примерах используется Excel 2013.
Получение данных
При установленной надстройке Power Query запустите Excel и щелкните на ленте Power Query. Предоставляется несколько вариантов получения внешних данных, в том числе Online Search («Поиск в сети»), расположенный в дальнем левом конце ленты. Эта функция обеспечивает произвольный поиск данных, точно такой же, как в поисковом механизме Интернета. Обратите внимание, что при этом не производится обход всего Интернета; поиск ограничен определенным набором сайтов, таких как Wikipedia и некоторые сайты правительства США. Кроме того, можно публиковать и искать собственные данные, когда Power Query используется в сочетании с новым «облачным» продуктом Microsoft Power BI for Office 365.
В приведенном примере выполняется подключение к общедоступному файлу Excel, содержащему данные о месячных продажах автомобилей в США. Прежде чем начать, выберите Options («Параметры») из группы Machine Settings («Параметры компьютера») на ленте, а затем Enable Advanced Query Editing («Включить расширенное редактирование запросов»). В группе Get External Data («Получить внешние данные») выберите From Web («Из Интернета»). В диалоговом окне From Web, показанном на экране 1, введите http://www.bea.gov/national/xls/gap_hist.xls в текстовом окне URL и нажмите OK.
.jpg) |
| Экран 1. Ввод URL-адреса для получения файла Excel |
На данном этапе Power Query уже определил, что введенный URL-адрес указывает на книгу Excel. Поэтому когда откроется «Редактор запросов», вы увидите все листы книги и настраиваемые диапазоны, перечисленные на панели Navigator слева.
В «Редакторе запросов» разверните панель Steps («Шаги») справа, затем прокрутите вниз и выберите лист Table 5 в панели Navigator, как показано на экране 2. Table 5 содержит данные о продажах тяжелых грузовиков. Обратите внимание, что в верхнем левом углу «Редактора запросов» указано имя запроса — Query1. Щелкните имя запроса правой кнопкой мыши, выберите пункт Rename («Переименовать») и введите новое имя — Vehicle Sales.
.jpg) |
| Экран 2. Выбор листа Table 5 |
Затем щелкните правой кнопкой мыши пиктограмму Table в верхнем левом углу панели просмотра (центральная панель) и выберите параметр Remove Top Rows («Удалить верхние строки»). В диалоговом окне Remove Top Rows, показанном на экране 3, введите значение 3 и нажмите кнопку OK.
.jpg) |
| Экран 3. Удаление трех верхних строк |
Вновь щелкните правой кнопкой мыши пиктограмму Table на панели просмотра и выберите параметр Use First Row As Headers («Использовать первую строку как заголовки»). Обратите внимание, что имена двух первых столбцов по-прежнему Column1 и Column2. Эти заголовки не меняются, так как были нулевыми значениями в первой строке этих двух столбцов. Дважды щелкните Column1 и переименуйте в MonthName. Затем дважды щелкните Column2 и переименуйте в Year.
Прокрутите до конца вправо панель просмотра и выделите все лишние столбцы (Column7, Column8, Column9 и Column10), щелкая их названия при нажатой клавише Shift. Щелкните правой кнопкой мыши, выделив столбцы, и выберите пункт Remove Columns («Удалить столбцы»).
Вернитесь к столбцу MonthName. Щелкните стрелку вниз рядом с заголовком и снимите флажок (null).
Остановимся на минуту, прежде чем выполнить несколько шагов для завершения запроса Vehicle Sales. Если вы аккуратно повторяли действия, редактор запросов должен выглядеть так, как показано на экране 4. Обратите внимание, что панель Navigator скрыта. Как показано на панели Steps, пока для этого запроса было сформировано шесть шагов. Щелкните первый шаг (Source). Обратите внимание, что панель просмотра возвращается к исходному состоянию листа Table 5. Если выбрать следующий шаг (RemovedFirstRows), вы увидите соответственно обновленную панель просмотра. Редактор запросов ведет себя как бортовой регистратор и даже обеспечивает «повторное воспроизведение» каждого шага по отношению к источнику данных.
.jpg) |
| Экран 4. Повторное воспроизведение шагов для запроса |
Каждый шаг в запросе, в сущности, представляет собой часть сценария. Если щелкнуть пиктограмму Script в правом верхнем углу панели просмотра, появится окно Edit Query («Изменение запроса») со сценарием запроса (см. листинг). Если пиктограмма Script отсутствует, убедитесь, что выбран режим Enable Advanced Query Editing, как показано выше. Сценарий можно изменить непосредственно в окне, но если вы планируете внести существенные изменения, рекомендуется сохранить копию исходного сценария. Закройте окно Edit Query, нажав кнопку Cancel («Отмена»).
Как отмечалось выше, запрос еще не завершен. Требуется добавить два новых столбца и внести еще несколько изменений, поэтому продолжим формирование запроса.
Щелкните правой кнопкой мыши в панели просмотра и выберите параметр Insert Column-Custom, чтобы вызвать диалоговое окно Insert Custom Column («Вставка пользовательского столбца»), показанное на экране 5.
.jpg) |
| Экран 5. Вставка Custom Column Formula |
В текстовом поле Custom Column Formula введите следующую формулу:
=Text.Replace(Text.Replace(Text.Replace(Text.Replace (Text.Replace(Text.Replace(Text.Replace(Text.Replace (Text.Replace(Text.Replace(Text.Replace(Text.Replace (Text.Start([MonthName], 3), «Jan», «1"),»Feb«, "2»), «Mar», «3"),»Apr«, "4»), «May», «5"),»Jun«, "6»), «Jul», «7"),»Aug«, "8»), «Sep», «9"),»Oct«, "10»), «Nov», «11"),»Dec«, "12»)
Power Query вводит новый язык формул для преобразования данных. Встроенной формулы для преобразования имени месяца в соответствующее число не существует, так что это делается с помощью приведенной конкретной формулы. Больше узнать о доступных функциях можно на веб-странице Learn about Power Query formulas (http://office.microsoft.com/en-us/excel-help/learn-about-data-explorer-formulas-HA104003958.aspx).
Нажмите кнопку OK, чтобы закрыть окно Insert Custom Column, и вы увидите новый столбец с именем Column1 на панели просмотра. Возможно, для этого придется выполнить прокрутку вправо. Необходимо переименовать данный столбец в MonthNumber. Как уже было показано, один из способов переименовать столбец — дважды щелкнуть заголовок столбца и ввести новое имя. Есть и другой способ переименовать столбец, который не требует дополнительного шага. Как показано на экране 6, можно выделить последний шаг на панели Steps и изменить имя столбца в текстовом поле функции. Обратите внимание, что этот шаг переименован в InsertMonthNumber, чтобы облегчить понимание запроса.
.jpg) |
| Экран 6. Замена имени столбца без добавления шага |
Затем щелкните правой кнопкой мыши в панели просмотра и вновь выберите параметр Insert Column-Custom. В текстовом поле Custom Column Formula («Формула пользовательского столбца») введите формулу:
=Date.EndOfMonth(Date.From([MonthNumber] & «/01/» & Text.From([Year]) ) )
Эта формула строит значение даты из года и номера месяца, а затем возвращает последний день месяца. Измените имя этого столбца на Date. Кроме того, измените тип данных на Date, щелкнув заголовок столбца правой кнопкой мыши и выбрав команду Change Type («Изменить тип»).
Также необходимо изменить тип данных четырех столбцов продаж на числовой. Эти столбцы имеют имена:
- Not seasonally adjusted (Thousands);
- Combined seasonal, trading-day factor;
- Seasonally adjusted (Thousands);
- Seasonally adjusted at annual rates (Millions).
Затем можно удалить столбцы Year, Month и MonthNumber, так как они больше не нужны. Как было указано выше, это можно сделать, щелкнув заголовок столбца правой кнопкой мыши и выбрав команду Remove Columns.
После того, как три столбца удалены, нужно переместить столбец Date на левый край панели просмотра. Это можно сделать, перетянув мышью заголовок столбца или щелкнув заголовок столбца правой кнопкой и выбрав команду Move («Переместить»).
Закройте редактор запросов, нажав кнопку Done («Готово»). На экране 7 показан фрагмент результатов, возвращенных запросом Vehicle Sales. Полный набор результатов приведен в образцовой книге Excel PowerQuery_Chessman_SQLPro_Example1.xlsx, которую можно загрузить, нажав кнопку Download the Code в верхней части страницы.
.jpg) |
| Экран 7. Анализ результатов запроса Vehicle Sales |
По умолчанию результаты запроса вставляются в таблицу Excel с тем же именем, что и запрос. Обратите внимание, что символы подчеркивания используются вместо всех пробелов в имени запроса. В Excel 2013 также предусмотрена команда Load to data model («Загрузить в модель данных») для загрузки результатов запроса в модель данных Excel. Модель данных в Excel 2013 в сущности представляет собой механизм Power Pivot, появившийся в Excel 2010. Дополнительные сведения о модели данных можно найти в статье «Принципы работы PowerPivot и Power View с Microsoft Excel 2013» (опубликованной в Windows IT Pro/RE № 6 за 2013 год). Обратите внимание, что результаты запросов, превосходящие 1 048 575 строк, требуется загрузить в модель данных и отключить параметр Load to worksheet («Загрузить в лист»).
Обновление запроса
В какой-то момент вам потребуется обновить результаты запроса. Существует три способа это сделать. Первый вариант — щелкнуть ссылку Refresh («Обновить») на панели Query Settings («Параметры запроса»), как показано на экране 7. Другой подход — использовать параметр Refresh All («Обновить все») в разделе Connections («Соединения») на ленте Data. Обратите внимание, что этот подход можно автоматизировать с помощью макросов Excel. Если загрузить запрос в модель данных Excel, появится возможность использовать кнопку Refresh из окна Power Pivot.
Добавление данных
Еще одна важная функция Power Query — добавление данных. Например, в общедоступном файле Excel, содержащем данные о месячных продажах автомобилей, числа продаж разбросаны по разным листам: продажи отечественных автомобилей на листе Table 1, продажи иностранных автомобилей на листе Table 2 и т.д. Чтобы получить полный и подробный отчет по продажам автомобилей каждой марки необходимо добавить данные из листов в единую таблицу, или на языке Power Query — в один запрос. Выполнить эту задачу можно с помощью параметра Append («Добавить») в группе Combine («Объединение») на ленте Power Query. В результате создается новый запрос, содержащий все строки из активного запроса (то есть запроса, в котором выбран параметр Append), за которыми следуют строки из запросов, которые нужно добавить. С помощью Append можно добавлять до двух запросов за раз. Для добавления более чем двух запросов необходимо вручную изменить сценарий Power Query.
Дополнительные сведения о добавлении данных можно найти на веб-странице Combine multiple queries (http://office.microsoft.com/en-us/excel-help/combine-multiple-queries-HA103993872.aspx#_Toc359735882). Чтобы увидеть пример добавленных запросов, загрузите книгу PowerQuery_Chessman_SQLPro_Example2_VehicleSalesAll.xlsx на сайте нашего журнала. В этой книге приведен пример двух запросов, соединенных с помощью параметра Append, а также запрос, в котором используется сценарий, измененный для добавления пяти запросов.
Отмена сведения данных
Использование сводных таблиц — превосходный способ просмотра данных (отсюда их популярность), но такой инструмент, как Power Pivot, рассчитан на простой табличный формат. Удобно, что Power Query позволяет легко отменить сведение данных. Например, на экране 8 показаны данные о разрешениях на жилищное строительство, в которых по строкам таблицы расположены годы, а по столбцам — месяцы.
.jpg) |
| Экран 8. Отмена сведения набора данных |
Выделив все столбцы Month, щелкните правой кнопкой мыши один из выделенных столбцов и выберите Unpivot Columns («Отменить сведение столбцов»). Таким образом можно быстро преобразовать данные в табличный формат, как показано на экране 9. Весь набор данных содержится в книге PowerQuery_Chessman_SQLPro_Example3_UnpivotingData.xlsx.
.jpg) |
| Экран 9. Анализ набора данных в табличном формате |
Недавно Microsoft объявила о Power BI for Office 365, «облачной» службе, с помощью которой компания может публиковать и совместно использовать собственные BI-артефакты, такие как книги Excel. Power Query позволяет опубликовать запрос к Power BI, чтобы другие пользователи могли искать и повторно задействовать эти запросы в собственных книгах Excel. Во время подготовки данной статьи Power BI была представлена предварительной версией, но одним из компонентов Power BI стал шлюз, благодаря которому опубликованные запросы могут содержать не только запросы к внешним данным, но и внутренние источники данных (например, SQL Server). Дополнительные сведения о Power BI можно найти на сайте Power BI for Office 365.
Изучайте Power Query
Поиск, преобразование и очистка данных — обычно довольно утомительная работа. Кроме того, процесс сбора данных часто занимает много времени. С помощью Power Query конечные пользователи могут без труда собирать, уточнять и объединять данные как из общедоступных, так и из частных источников. Я рекомендую освоить Power Query и внедрить продукт для конечных пользователей вашей компании.
let
Source = Excel.Workbook(Web.Contents
(«http://www.bea.gov/national/xls/gap_hist.xls»)),
#«Table 5» = Source{[Name=«Table 5»]}[Data],
RemovedFirstRows = Table.Skip(#«Table 5»,3),
FirstRowAsHeader = Table.PromoteHeaders(RemovedFirstRows),
RenamedColumns = Table.RenameColumns(FirstRowAsHeader,
{{«Column1», «MonthName»}, {«Column2», «Year»}}),
RemovedColumns = Table.RemoveColumns(RenamedColumns,
{«Column7», «Column8», «Column9», «Column10»}),
FilteredRows = Table.SelectRows(RemovedColumns, each
([MonthName] <> null))
in
FilteredRows