Это наш очередной пост в колонку “Office как Платформа”. На этот раз приветствуем Дмитрия Соловьева ( dmitriysolovev), руководителя центра поддержки Office 365 в компании АстроСофт – автора уже нескольких статей по технологии PowerBI. В этой статье Дмитрий расскажет о том, как использовать REST API для получения данных из внешних источников для визуализации и анализа в PowerBI. – Владимир Юнев

Всем привет! На хабре было уже несколько постов на тему Power BI. Если судить по комментариям, то как минимум нескольким читателям актуальна тема получения данных из REST веб-сервисов. Сегодня ее и рассмотрим на примере получения и обработки данных с портала открытых данных г. Санкт-Петербурга (http://data.gov.spb.ru/). Экзекуции будут подвергнуты данные технико-экономических паспортов многоквартирных домов (http://data.gov.spb.ru/datasets/69/). В роли «пыточного инструмента» выступит Excel 2016, в состав которого теперь по умолчанию встроен Power Query.

Введение

В целом Power Query позволяет получать данные из открытых и не очень источников различными способами (рис.1):

1. Получение данных «Из интернета». В этом случае достаточно указать URL страницы, на которой находятся интересующие нас данные и загрузить их. Из недостатков – стабильно работает только на страницах, использующих табличную вёрстку; приходится писать функции в случае, если данные разбиты на страницы; администраторы сайта могут Вас забанить, если будет слишком много запросов (а они будут, если страниц много);
2. Получение данных из канала OData. Этот вариант подходит для различных LOB-приложений, которые поддерживают OData;
3. Написать запрос с нуля, используя возможности языка «M». Как раз этот вариант я и буду использовать в данном случае.

Рис.1. — Варианты источников данных Power Query

Часть 1. Проба сервиса на вкус

В большинстве случаев для того, чтобы работать с REST API требуется получить API Token. Для его получения на сайте открытых данных г. Санкт-Петербурга требуется зарегистрировать учётную запись разработчика, затем токен отобразится в личном кабинете. Также на сайте можно найти краткую инструкцию по использованию API (http://data.gov.spb.ru/developers/). Обращаю внимание на формат передачи токена, поскольку эта информация мне понадобится, чтобы получить доступ к нужной мне информации (рис.2).


Рис.2. — Документация к API

Итак, у меня есть пример использования API, есть токен и теперь можно попробовать получить пробную информации с сайта открытых данных г. Санкт-Петербурга. Первым делом я выбираю тип запроса «Из Интернета» и в открывшемся окне указываю адрес: http://data.gov.spb.ru/api/v1/datasets/ (рис.3).


Рис.3. — Запрос данных Из Интернета

После нажатия кнопки «ОК» ожидаемо вижу окно авторизации с сообщением о том, что используются недопустимые учетные данные. К слову, Power Query поддерживает несколько типов учетных данных при работе с внешними запросами, среди которых есть и Web API. Этот тип авторизации позволяет передать токен, что я и попытаюсь сделать. Немного подумав, Power Query опять выдает сообщение о том, что я использую неверные учетные данные. Позднее нужно будет проанализировать – в каком формате Power Query передает API ключ и что с этим можно делать (рис.4).


Рис.4. — Ошибка авторизации Power Query

Так как простой способ подключения к API результата не принёс, то придется пойти более сложным и сформировать запрос вручную. Для этого создадим пустой запрос и откроем расширенный редактор (рис.5):


Рис.5. — Запуск расширенного редактора запросов

Как Вы уже знаете, «за внешней оболочкой» Power Query лежит свой язык программирования M. Для получения данных из веб-источников используется функция Web.Contents, которая принимает два параметра – Url веб-сайта и набор параметров запроса (API ключ, таймаут подключения, заголовки запроса и т.д.). Возвращает эта функция набор двоичных данных, которые затем можно обработать с использование других функций языка М. Сформируем тестовый запрос, который должен вернуть набор доступных на портале наборов данных:

Web.Contents("http://data.gov.spb.ru/api/v1/datasets/ ", [Headers=[#"Authorization"="Token c81a**************************fe3"]])

После ввода текста запроса нужно нажать кнопку «Готово» и просмотреть полученный результат (рис.6).


Рис.6. — Пример тестового запроса к API

Результаты тестового запроса показаны на рисунке 7. Поскольку функция Web.Contents возвращает результат своей работы в виде двоичных данных, то логично, что на экране мы видим значок, а не цифры. Для того, чтобы увидеть результаты в более доступном для человека виде, нужно дважды кликнуть на значок (рис.7 и рис.8).


Рис.7. — Результаты тестового запроса к API


Рис.8. — Развернутые двоичные данные

Внешний вид редактора запросов после «проваливания» в результаты работы функции Web.Contents можно увидеть на рисунке 8. Обратите внимание на строку формул, в которой видна функция Json.Document(Source). Функция Json.Document возвращает содержимое Json документа, а в качестве аргумента в нее передан результат выполнения предыдущего шага с именем «Source». Да, язык M выполняет пошаговую обработку кода, при этом каждый шаг должен иметь свое имя. В случае работы с запросами в режиме обычного редактора все шаги отображаются в правой части экрана и при необходимости могут быть изменены. Правда с изменением стоит быть поосторожнее, т.к. при последовательном выполнении шагов это тоже самое, что и вмешательство в прошлое, со всеми вытекающими последствиями.

Результат запроса имеет вид «Список». В рамках данной статьи мы не разбираем различия между списками и таблицами в Power Query, поэтому скажу, что для дальнейшей работы с данными нам потребуется преобразовать результаты запроса в таблицу и затем развернуть содержимое столбцов. Итоговый внешний вид полученных данных видно на рисунке 10. Как и обещано в документации к API мы получили полный перечень наборов данных и их идентификаторов (рис.9 и рис.10).


Рис.9. — Преобразование в таблицу


Рис.10 — Перечень источников данных с их идентификаторами

Если открыть расширенный редактор запросов, то мы увидим, что для всех выполненных нами шагов был автоматически сгенерирован код на языке «М» (рис.11).

let
    Source= Web.Contents("http://data.gov.spb.ru/api/v1/datasets/", [Headers=[#"Authorization"="Token c81*******************************fe3"]]),
    #"Импортированные данные JSON" = Json.Document(Source),
    #"Преобразовано в таблицу" = Table.FromList(#"Импортированные данные JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Развернутый элемент Column1" = Table.ExpandRecordColumn(#"Преобразовано в таблицу", "Column1", {"id", "name"}, {"id", "name"})
in
    #"Развернутый элемент Column1"

Рис.11. — Код в расширенном редакторе запросов

На этом завершу первую пробу API и перейду к формированию второго запроса.

Часть 2. Получаем данные ТЭП домов Санкт-Петербурга

После недолгого изучения документации к API стало ясно, что для получения нужных данных нам потребуются два запроса к API:

• http://data.gov.spb.ru/api/v1/datasets/69/versions/latest/ — возвращает описание данных в последней версии набора данных
• http://data.gov.spb.ru/api/v1/datasets/69/versions/latest/data/ — возвращает данные, содержащиеся в последней версии.

В данном случае 69 это идентификатор набора данных, который мы можем получить либо в результатах нашего первого запроса к API, либо открыв страницу нужного набора в браузере и просмотрев URL (рис.12).


Рис.12. — ИД набора данных в URL-адресе

В результате я получаю данные с описанием полей набора данных и сам набор данных о технико-экономических паспортах домов, который после очистки можно будет визуализировать в Power BI или проанализировать при помощи Power Pivot и сводных таблиц в Excel (рис.13 и рис.14).


Рис.13. — Описание набора данных


Рис.14. — Набор данных ТЭП домов Санкт-Петербурга

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

Дополнительные ссылки

Посмотреть основную документацию по созданию офисных приложений вы можете на портале Центра Разработки Office, также вы можете скачать примеры готовых приложений.

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

Об авторе

Дмитрий Соловьёв (MCSA, MCITP, MCT)
Руководитель центра поддержки Office 365 в компании АстроСофт.
dmitriysolovev

Около 10 лет работал с продуктами корпорации Microsoft в качестве инженера, разработчика и тренера. Последние четыре года специализируется на внедрении систем на базе SharePoint, Exchange, Office 365, Microsoft Azure. Магистр по специальности «Техническая эксплуатация летательных аппаратов и их систем»