Офис как Платформа, выпуск №3: Power BI. Получаем данные из REST API13.08.2015 14:19

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

0dc9e377af8142e2ab5b7dbc5488de36.png


Всем привет! На хабре было уже несколько постов на тему 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». Как раз этот вариант я и буду использовать в данном случае.


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

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


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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

d8fad848a0fb485aa6a9379f2c12bfee.png
Рис. 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"


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

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

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


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

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

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

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

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

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

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


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

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

Об авторе

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

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

Только зарегистрированные пользователи могут участвовать в опросе. Войдите, пожалуйста.

© Habrahabr.ru