Блог

Общая информация: Power Pivot, Power Query, Power View для Excel

Компания Microsoft создала три средства для получения, обработки и представления данных в Microsoft Excel. Все они призваны упростить пользователям процессы анализа, моделирования и визуализации данных.

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

Power Pivot — это технология моделирования данных, которая позволяет создавать модели данных, устанавливать отношения и добавлять вычисления. С помощью Power Pivot можно работать с большими наборами данных, создавать развернутые отношения и сложные (или простые) вычисления.

Power View — это технология визуализации данных, с помощью которой можно создавать интерактивные диаграммы, графики, карты и другие наглядные элементы, позволяющие «оживить» информацию.

Использование Power Query в Microsoft Excel для доступа к данным наблюдений

Для начала использования Power Query в Excel необходимо скачать и установить последнюю версию инструменты с сайта Microsoft.

Power Query (https://www.microsoft.com/ru-ru/download/details.aspx?id=39379)

Power Query поддерживает следующие версии Office:

  • Microsoft Office 2010 Professional Plus c Software Assurance
  • Microsoft Office 2013

Процесс инсталляции Power Query

Для начала процесса инсталляции пакета запустить исполняемый файл установки PowerQuery_2.27.4163.242 (32-bit) [ru-RU].msi.

Далее появится диалог приветствия (Рисунок 1), на котором необходимо нажать кнопку «Далее».

 

Рисунок 1. Диалог приветствия инсталлятора

Рисунок 2. Пользовательское соглашение

Окно пользовательского соглашения (Рисунок 2). В Окне пользовательского соглашения необходимо прочитать и текст лицензионного соглашения и согласиться с ним, поставив галку в строке «Я принимаю условия лицензионного соглашения» и нажать на кнопку «Далее».

Рисунок 3. Конфигуратор установки

После чего появится диалог конфигуратора установки (Рисунок 3) пакета Power Query в котором можно изменить путь установки либо оставить все по умолчанию и нажать на кнопку «Далее».

Рисунок 4. Начало установки

В диалоге подтверждения начала установки необходимо нажать кнопку «Установить».

Meteo

Рисунок 5. Экран завершения установки Power Query

Окно завершения (Рисунок 5) инсталляции пакета сообщает что установка закончена и необходимо нажать на кнопку «Готово».


Формирование запросов

Для формирования запросов к серверу необходимо знать его адрес (доменное имя или IP) и порт. Все запросы к серверу будут содержать адрес, имя источника данных (команду), параметры фильтра данных. Соответствующий список команд указан в документе «Спецификация доступа к данным REST» (предоставляется по запросу).

Пример ссылки для запроса списка станции с сервера по адресу 192.168.1.1 и порт 8640 :

  • http://192.168.1.1:8640/stations.json

Параметры запросов

Запрос get имеет параметры, которые позволяют фильтровать получаемые данные. Параметры запроса указываются в соответсвии с передачей парметров запросом HTTP GET, а именно после имени источника запроса ставится знак «?». Параметры указваются парами - название праметра символ «=» значение. Например:

  • station=12345

Если требуется указать несколько параметров, то пары скрепляются символом «&». Например

  • station=12345&code=12101

Параметры времени «notbefor» и «notafter» используют формат UNIX-time. Для перевода даты формат UNIX-time можно воспользоваться следующей формулой Excel:

  • =("10.12.15"-25569)*86400)

Обратная формула, которая переводит UNIX-time в формат времени Excel:

  • = (1449792000 / 86400) + 25569

Зеленым выделена желаемая к переводу дата.

Пример запроса с get, который запрашивает данные тепмератры воздуха на высоте 2 метра по станции 27725 за период с 10.12.2015 по 11.12.2015:

  • (http://localhost:8640/get?station=27725&code=12101&hash=795976906&notbefor=1449792000&notafter=1449705600

Запрос данных с помощью Power Query в Microsoft Excel

Необходимо открыть новый документ Microsoft Excel и перейти на вкладку Power Query ленты приложения.

Meteo

Рисунок 6. Открытие вкладки Power Query

Сформированную ссылку запроса необходимо использовать в функции запрос данных из интернета. Для начала использования функции необходимо нажать на кнопку «Из интернета» (Рисунок 7). После чего появится диалоговое окно (Рисунок 8), в которое необходимо ввести адресс запроса к серверу.

Meteo

Рисунок 7. Вызов функции запрос данных из интернета

Meteo

Рисунок 8. Ввод строки запроса к серверу

После ввода запроса нажать кнопку «ОК», после этого появится окно редактора запросов Power Query.

Meteo

Рисунок 9. Редактор запросов Power Query

Далее в редакторе необходимо нажать кнопку «В таблицу» (Рисунок 10). После этого появится диалог настроек преобразования (Рисунок 11).

Meteo

Рисунок 10. Преобразование полученных данных в таблицу

Meteo

Рисунок 11. Настройки преобразования в таблицу

В диалоге настроек оставляем все по умолчанию и нажимаем «ОК». После закрытия данные будут преобразованы в таблицу как показано на рисунке Рисунок 12.

Meteo

Рисунок 12. Результат преобразование данных в таблицу

Для завершения преобразования, нужно выбрать колонки, которые будут отображаться в финальной таблице, нажав на кнопку, показанную на Рисунке 13.

Meteo

Рисунок 13. Выбор колонок для отображения в результирующей таблице

После чего появится диалог выбора необходимых колонок

Meteo

Рисунок 14. Выбор колонок для вывода

После выбора колонок необходимо нажать кнопку «ОК». В результате отобразится финальная таблица, которую можно выгрузить в лист Excel нажав кнопку «Закрыть и загрузить» ()

Meteo

Рисунок 15. Выгрузка таблицы запроса в лист Excel

Результат выгрузки данных в лист Excel показан на рисунке 16

Meteo

Рисунок 16. Результат загрузки данных станций с использованием Power Query

Аналогично делается запрос на получение списка параметров, зарегистрированных в ЦСДН. Ниже показан комплект данных, полученный по запросу:

  • http://localhost:8640/measurement.json

Результат такого запроса изображен на рисунке 17.

Meteo

Рисунок 17. Результат получения списка параметров, зарегистрированных в ЦСДН

Таким же способом выполняется запрос на получение данных из ЦСДН. Ниже показан комплект данных, полученный по запросу:

  • http://localhost:8640/get?station=27725&code=12101&hash=795976906&notbefore=1449792000&notafter=1449705600

Meteo

Рисунок 18. Результат запроса данных

ООО "МЕТЕОКОНТЕКСТ"

Российский разработчик уникального метеорологического программного обеспечения.

info@meteoctx.ru  8 (495) 380-30-56

Подписаться на рассылку