Глава 13. Загрузка данных из Exchange в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

Ris. 13.1. Okno podklyucheniya k Microsoft Exchange

Рис. 13.1. Окно подключения к Microsoft Exchange

Скачать заметку в формате Word или pdf

Статистическая обработка данных опроса. С помощью Power Query вы можете создать опрос с таблицей в теле письма и отправить его аудитории. Затем вы можете использовать Power Query для чтения ответов, извлечения информации из отдельных таблиц и объединения таблиц в источник данных. Вам также не нужно беспокоиться, если кто-то отправит ответ позже. Вы просто кликните Обновить.

Агрегирование бюджетов. Вы можете отправить книги Excel по почте и попросить сотрудников заполнить информацию по своему подразделению. Power Query найдет письма с определенной темой и/или вложением, а затем откроет книги Excel и объединит их в единый набор данных.

Дашборд алертов. Если вы по электронной почте получаете различные уведомления от серверов и оборудования, вы можете использовать Power Query для агрегирования сообщений, построения диаграмм объема и серьезности проблем и создания информационной панели (дашборда).

Подключение к Microsoft Exchange

Этот пример вы сможете реализовать, если ваша рабочая почта реализована с помощью Microsoft Exchange. Создайте новую книгу Excel. Пройдите по меню Данные –> Получить данные –> Из других источников –> Из Microsoft Exchange (рис. 13.1). Введите свой корпоративный адрес с указанием домена. Нажмите Ok. В окне авторизации введите пароль, нажмите Подключение.

Ris. 13.2. Okno avtorizatsii dostupa k Microsoft Exchange

Рис. 13.2. Окно авторизации доступа к Microsoft Exchange (поскольку пример основан на моей корпоративной почте, я удалил всю конфиденциальную информацию)

Появится окно службы автообнаружения Microsoft Exchange. Кликните Разрешить.

Ris. 13.3. Okno sluzhby avtoobnaruzheniya Microsoft Exchange

Рис. 13.3. Окно службы автообнаружения Microsoft Exchange

Как правило, служба автообнаружения уже включена и настроена сотрудниками ИТ-отдела вашей компании. Благодаря этому вы можете удаленно получать доступ к своей электронной почте с мобильных телефонов.

После небольшой задержки, вызванной подключением Power Query к серверу Exchange, вы попадете в окно Навигатор. Выберите Mail –> Изменить.

Ris. 13.4. Okno Navigator

Рис. 13.4. Окно Навигатор

Не нажимайте кнопку Загрузить после выбора таблицы Mail. Ожидание будет очень долгим, т.к. Power Query будет загружать весь ваш почтовый ящик!

Ris. 13.5. Okno redaktora Power Query s tablitsej pisem

Рис. 13.5. Окно редактора Power Query с таблицей писем

Чтобы уменьшить количество писем для анализа, вы перенаправили нужные письма в папку _in. Теперь вам нужно отфильтровать столбец путь к папке Folder Path. Интересно, что при попытке установить фильтр нужная папка не отображается:

Ris. 13.6. Nuzhnaya papka ne otrazhaetsya v spiske dostupnyh

Рис. 13.6. Нужная папка не отражается в списке доступных

Кликните Загрузить еще… в правом нижнем углу. По умолчанию этот список сокращается до ограниченного объема данных, отображаемых в предварительном просмотре, но при нажатии кнопки отображаются все доступные опции. Когда список появляется в полном объеме, выбираете нужную папку, нажмите Ok. Список писем отфильтруется.

Таблица содержит около 20 столбцов, включая такие полезные поля, как To, CC, Sender, Attachments и многое другое. Для целей нашего исследования требуются, только поля DateTimeSent (время отправки) и Body (тело письма). Выбираете эти два столбца и кликните Удалить другие столбцы:

Ris. 13.7. Ostalos dva stolbtsa

Рис. 13.7. Осталось два столбца

Столбец Body содержит полный текст письма, и его можно рассматривать как отдельные ячейки таблицы. Поскольку столбец Body содержит стрелку расширения в правом верхнем углу, вы можете развернуть эти записи в столбец. Оказывается, что за текстом скрывается два столбца:

Ris. 13.8. Stolbtse Body mozhet byt razvernut v dva stolbtsa

Рис. 13.8. Столбце Body может быть развернут в два столбца

Поскольку работа с текстом намного проще, чем с HTML, оставьте только текстовое поле. Обработайте столбец TextBody: выделите его и кликните правой кнопкой мыши Преобразование –> Усечь и еще раз Преобразование –> Очистить:

Ris. 13.9. Obrabotannye otobrannye pisma

Рис. 13.9. Обработанные отобранные письма (здесь и далее я использую оригинальные примеры из книги, поскольку лишь они содержат нужную для изложения специфику); чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Сейчас содержание писем может быть сокращено, чтобы получить интересующую нас информацию о суммах. Один из замечательных интерфейсов Power Query – окно предварительного просмотра, которое появляется внизу при выборе любой ячейки. Вы можете не только увидеть данные, но и выбрать их в этом окне и скопировать. Вместо того, чтобы разделить столбец TextBody по знаку $ можно выполнить следующие действия:

  • Скопируйте текст из окна предварительного просмотра, начиная с Hi и заканчивая $
  • Щелкните правой кнопкой мыши столбец TextBody –> Замена значений…
  • Вставьте скопированную текстовую строку в поле Значение для поиска
  • Оставьте пустым поле Заменить на
  • Нажимать OK

Ris. 13.10. Stolbets TextBody teper nachinaetsya s summy

Рис. 13.10. Столбец TextBody теперь начинается с суммы

Теперь можно разделить столбец TextBody по разделителю – пробелу, и удалить правый столбец. Установите тип данных – десятичное число, переименуйте столбе, и вы останетесь со следующими данными:

Ris. 13.11. Dannye poluchennye iz elektronnoj pochty ochishhennye i gotovye k ispolzovaniyu

Рис. 13.11. Данные, полученные из электронной почты, очищенные и готовые к использованию

Главная –> Закрыть и загрузить. Постройте сводную диаграмму:

Ris. 13.12. Diagramma pribyli postroennaya na dannyh teksta elektronnoj pochty

Рис. 13.12. Диаграмма прибыли, построенная на данных текста электронной почты

Рекомендации для источников Exchange

При построении решения на основе службы Microsoft Exchange необходимо учитывать два нюанса: переносимость и производительность. Планируете ли вы передать решение другому пользователю? Поскольку решение основано на вашей учетной записи электронной почты, использовать его можете только вы. Это решение не переносимо, так как основано на электронных письмах вашего почтового ящика.

Другим важным соображением является производительность. Поскольку почтовые ящики слабо структурированы, и охватывают годы, извлечение данных из Exchange может быть очень медленным. Решение этой проблемы заключается в использовании правил для перенаправления ключевых сообщений электронной почты во вложенные папки. Когда вложенные папки содержат только соответствующие данные, извлечение, преобразование и загрузка готового решения будет происходить гораздо быстрее.


Прокомментировать