Общая информация: 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. Начало установки
В диалоге подтверждения начала установки необходимо нажать кнопку «Установить».
Рисунок 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¬befor=1449792000¬after=1449705600
Запрос данных с помощью Power Query в Microsoft Excel
Необходимо открыть новый документ Microsoft Excel и перейти на вкладку Power Query ленты приложения.
Рисунок 6. Открытие вкладки Power Query
Сформированную ссылку запроса необходимо использовать в функции запрос данных из интернета. Для начала использования функции необходимо нажать на кнопку «Из интернета» (Рисунок 7). После чего появится диалоговое окно (Рисунок 8), в которое необходимо ввести адресс запроса к серверу.
Рисунок 7. Вызов функции запрос данных из интернета
Рисунок 8. Ввод строки запроса к серверу
После ввода запроса нажать кнопку «ОК», после этого появится окно редактора запросов Power Query.
Рисунок 9. Редактор запросов Power Query
Далее в редакторе необходимо нажать кнопку «В таблицу» (Рисунок 10). После этого появится диалог настроек преобразования (Рисунок 11).
Рисунок 10. Преобразование полученных данных в таблицу
Рисунок 11. Настройки преобразования в таблицу
В диалоге настроек оставляем все по умолчанию и нажимаем «ОК». После закрытия данные будут преобразованы в таблицу как показано на рисунке Рисунок 12.
Рисунок 12. Результат преобразование данных в таблицу
Для завершения преобразования, нужно выбрать колонки, которые будут отображаться в финальной таблице, нажав на кнопку, показанную на Рисунке 13.
Рисунок 13. Выбор колонок для отображения в результирующей таблице
После чего появится диалог выбора необходимых колонок
Рисунок 14. Выбор колонок для вывода
После выбора колонок необходимо нажать кнопку «ОК». В результате отобразится финальная таблица, которую можно выгрузить в лист Excel нажав кнопку «Закрыть и загрузить» ()
Рисунок 15. Выгрузка таблицы запроса в лист Excel
Результат выгрузки данных в лист Excel показан на рисунке 16
Рисунок 16. Результат загрузки данных станций с использованием Power Query
Аналогично делается запрос на получение списка параметров, зарегистрированных в ЦСДН. Ниже показан комплект данных, полученный по запросу:
- http://localhost:8640/measurement.json
Результат такого запроса изображен на рисунке 17.
Рисунок 17. Результат получения списка параметров, зарегистрированных в ЦСДН
Таким же способом выполняется запрос на получение данных из ЦСДН. Ниже показан комплект данных, полученный по запросу:
- http://localhost:8640/get?station=27725&code=12101&hash=795976906¬before=1449792000¬after=1449705600
Рисунок 18. Результат запроса данных
