Tableau de bord Excel basé sur Microsoft Exchange

Publié le 11 janvier 2016
par Sophie Marchand M.Sc., CPA, CGA, MVP
Calendrier Microsoft Exchange

Tableau de bord Excel basé sur Microsoft Exchange

Il est possible de créer un tableau de bord dans Excel à partir de données enregistrées dans Microsoft Exchange. Il existe plusieurs façons pour récupérer les données de Microsoft Exchange. L’une des façons les plus récentes, est certainement celle proposée par Power Query. Cet article vise à vous montrer comment utiliser Power Query pour créer un tableau de bord dans Excel, basé sur des données Microsoft Exchange et à vous donner quelques conseils en la matière.

 

Exemple de données récupérées via Microsoft Exchange

Pour chaque formation que vous désirez suivre avec le CFO masqué, on vous recommande de passer un test en ligne. Par la suite, vous recevez un courriel avec le résultat obtenu sur le test en question et une recommandation quant à la formation ou aux formations que vous devriez suivre. Ce courriel ressemble à ceci:

Exemple de courriel

 

Exemple de tableau de bord Excel

Les courriels ci-haut sont générés automatiquement par notre système maison (du bon vieux VBA!). Nous pouvons donc récupérer ces courriels, plus particulièrement la note à l’intérieur de chacun des courriels, afin de bâtir le tableau de bord suivant. Par exemple, au mois de janvier 2016, à ce jour, 27 personnes ont effectué le test en ligne de la formation Excel – Mise à niveau. Parmi elles, 3 ont obtenu 30%, 4 ont obtenu 40%, etc.

Calendrier Microsoft Exchange

 

Se connecter à Microsoft Exchange

Pour se connecter à Microsoft Exchange à partir de Power Query, il faut se rendre dans la section From Other Sources/À partir d’autres sources, tel qu’illustré ci-bas:

Power Query from Microsoft Exchange

 

Ce qui peut être extrait de Microsoft Exchange

Tel qu’illustré ci-dessous, les données accesssibles dans Microsoft Exchange sont les courriels, les calendriers, les personnes (contacts), les tâches et les invitations à des réunions.

Calendrier Source Microsoft Exchange

 

Notre exemple est basé sur des courriels envoyés. Donc, une fois connecté à Microsoft Exchange, via Power Query, nous devons choisir Mail. Mais attention de ne pas choisir l’option Load (ou Charger), car dans ce cas, tous les courriels seront chargés, ce qui peut prendre une éternité. Il est largement préférable de cliquer immédiatement sur Edit (ou Modifier).

Power Query on Microsoft Exchange

 

Transformations dans Power Query

Une fois l’option Mail sélectionnée, nous avons obtenu une table avec les colonnes suivantes:

  • Folder Path
  • Subject
  • Sender
  • DisplayTo
  • DisplayCc
  • ToRecipients
  • CcRecipients
  • BccRecipients
  • DateTimeSent
  • DateTimeReceived
  • Importance
  • Categories
  • IsRead
  • HasAttachments
  • Attachments
  • Preview
  • Attributes
  • Body
  • Id

 

Si vous avez bien examiné la liste des items énumérés ci-dessus, vous avez sans doute aperçu la mention de Attachments. Cela est plutôt intéressant et pratique. Par exemple, penser à un projet d’entreprise où tout le monde vous envoie par courriel des données dans un document Excel standard, (données de budget, feuilles de temps, compte de dépenses, etc.). Imaginez maintenant que vous utilisiez tout simplement Power Query pour récupérer automatiquement toutes les données de ces fichiers-joints… Quand même pas mal, non?

 

Voici ensuite la série de transformations que nous avons enregistrées dans Power Query.

 

Nous avons filtré nos sujets de courriels pour ne retenir que ceux commençant par Test en ligne.

Calendrier Microsoft Exchange Filtre

 

Nous avons ensuite choisi de conserver les colonnes suivantes:

Calendrier Microsoft Exchange Remove Columns

 

Nous avons deux options pour récupérer les données de la colonne Body, soit le format texte ou le format html. Nous avons choisi le format texte.

Calendrier Microsoft Exchange Expand Body

 

Nous avons ensuite remplacé certaines valeurs par des espaces vides, soit les valeurs avant la mention du résultat au test.

Calendrier Microsoft Exchange Replace Values

 

Nous avons obtenu une colonne avec ce qui semblait, à prime abord, être des cellules vides, mais en cliquant sur l’une de ces cellules, on a pu voir un aperçu du contenu au bas de notre écran. Comme nous avions besoin de récupérer la note obtenu au test, nous avons d’abord supprimé tout l’espace vide devant cette note.

Calendrier Microsoft Exchange Trim Text

 

Pour supprimer l’espace vide, nous avons utilisé la fonction Trim, ce qui nous a permis d’obtenir ce qui suit:

Calendrier Microsoft Exchange Trim Text 2

 

Pour nous débarrasser de tout le reste, nous avons utilisé la fonctionnalité Split Column By et nous avons ensuite renommer nos colonnes et changer les types de données.

Split Column_Change Type_Remove Columns_Rename Columns

 

Nous avons ensuite voulu convertir les nombres entiers en %. Pour ce faire, nous avons choisi d’ajouter une colonne personnalisée:

Calendrier Microsoft Exchange Colonne personnalisée

 

Finalement, nous avons obtenu une table avec les courriels des utilisateurs, les dates et les notes des résultats de tests. C’est ce qui nous a permis de créer le tableau de bord présenté en début d’article.

Calendrier Microsoft Exchange Résultat Final

 

À prendre en considération

Une solution comme celle présentée ci-haut peut être intéressante mais il faut être vigileant en milieu corporatif. En effet, cette solution s’alimente de données contenues dans un compte personnel, dans ce cas-ci, mon compte personnel de courriels. Si un individu développe une telle solution en entreprise, la solution ne peut pas être transféfée à un autre individu puisque la requête se fera alors à partir du compte de cet individu (qui n’aura certainement pas le même contenu que le premier). Donc, si vous songez à développer une solution corporative, basée sur Microsoft Exchange, vous devez prendre en considération cet élément. Vous pourriez songer à transférer les archives courriels d’un employé vers un autre (dans un cas extrême) ou vous pourriez songer à exporter l’info d’abord sur une plate-forme indépendante et à faire pointer vos requêtes vers cette plate-forme. Dans tous les cas, c’est un pensez-y bien.

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

2 réflexions sur “Tableau de bord Excel basé sur Microsoft Exchange”

  1. Bonjour,
    Est-ce que ce type de connexion peut se faire sur des dossiers publics contenant des formulaires et ainsi accéder aux champs de ces formulaires. Nous avons développé, il y a plusieurs années, un formulaire Outlook pour gérer nos bogues. Chaque mois, nous transférons des données de ces formulaires dans des fichiers Excel pour créer des tableaux de bord sur la stabilité de notre application. Ça faciliterait beaucoup de le travail si on pouvait utiliser Power Query. Je sais que les public folders sont amenés à disparaître, mais je me demandais tout de même si c’était possible.
    Merci!

    1. Bonjour Joanne,

      Quand on se connecte à Microsoft Exchange, via Power Query, on a accès aux Mails, Calendars, People, Tasks, et Meetings Requests. Et dans les mails, on a accès au Inbox/Junk Mail/Deleted Items/. On n’a pas accès à nos folders locaux, qu’ils soient personnels ou publics. Donc, je n’ai pas exploré cette option mais à prime abord, je ne crois pas que ce serait possible de se connecter au folder en question via Microsoft Exchange. Toutefois, dépendamment du format des données collectées par votre formulaire, il serait peut-être possible d’utiliser Power Query pour se connecter directement aux données de ce folder.

      Au plaisir,

      Sophie

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top