Une des particularités d’Excel 2016 est de proposer un ensemble d’outils directement intégrés pour gérer vos données. Ces outils existaient jusqu’alors mais ils correspondaient à des add-in à installer ou à paramétrer.
Voici une liste de ces outils qui forment la suite Power BI d’Excel (à ne pas confondre avec PowerBI Desktop, outil Microsoft sorti en juillet dernier):
- PowerQuery
- PowerPivot
- PowerView
- PowerMap (Excel 2013) ou Carte 3D (Excel 2016)
PowerQuery
Historique
Disponible depuis Excel 2010 mais jusqu’alors, il fallait le télécharger et l’installer. Depuis la version 2016, il est directement intégré.
Finalité
PowerQuery permet d’Extraire des données depuis de très nombreuses sources (Bases de données, Web, Excel, CSV, XML, …) , de les Transformer (Ajout de colonnes, fusion, nettoyage, mises à jour …) et de les charger dans un modèle de données (PowerPivot) ou dans Excel selon les besoins.
Exemple
Voir article pour importer les cours de devises grâce à PowerQuery.
Voici un exemple en quelques images de ce que nous pouvons faire avec PowerQuery. Ici, il s’agit de charger une table d’une base de données Access, de supprimer les colonnes inutiles puis d’ajouter deux fichiers Excel contenant la liste de codes magasins et le lieux géographiques. L’objectif est de visualiser l’évolution du chiffre d’affaire par Ville.
Import de tables:
Requêtes à transformer:
Charger dans un modèle de données:
Chargement des requêtes:
PowerPivot
Historique
PowerPivot (PP), tout comme PowerQuery (PQ), est également disponible depuis Excel 2010. Les premières versions étaient peu stables mais PP s’est grandement amélioré au fil des années et la version 2016 est réussie.
Intégré depuis Excel 2013, il fallait toutefois la version PRO au début. Une mise à jour a été faite en août 2013 afin que toutes les versions puissent en bénéficier gratuitement comme cela était le cas avec la version 2010. Toutefois, j’ai testé sur la version « Famille et étudiant » sans succès.
Il faut également savoir que la compatibilité n’est pas toujours (voir rarement) au rendez-vous entre les diverses versions.
Finalité
PowerPivot est un outil d’analyse de type Business Intelligence. Il permet de stocker un grand volume de données (plusieurs millions de lignes) depuis des sources différentes, de créer des liens entre ces données, de créer de nouveaux calculs exploitables dans Excel.
Les données sont stockées dans la mémoire vive de votre ordinateur et cela permet d’avoir des calculs rapides, des tableaux croisés dynamiques et graphiques plus puissants.
La combinaison avec PowerQuery est très performante puisque les données importées dans PowerPivot ont été transformées, nettoyées et déjà extraites depuis de nombreuses sources différentes (plus nombreuses que celles proposées dans PP). Grâce à PowerQuery, on peut également passer des paramètres à PowerPivot (sur les dates par exemple).
Microsoft recommandé d’avoir au moins 4G de RAM. Par expérience, si vous pouvez choisir, préférez au moins 8G et si vous souhaitez traiter beaucoup de données, préférez également une version d’office en 64bits et non en 32bits.
Exemple
Nous reprenons l’exemple créé pour PowerQuery et nous décidons d’importer les données dans PowerPivot ; l’objectif étant de créer les liens entre nos différentes sources.
Accéder au modèle de données (à PowerPivot):
Copie d’écran de données dans PowerPivot:
Diagramme des relations dans PowerPivot:
PowerView
Historique
PowerView est apparu avec Excel 2013 mais il a quasiment disparu avec Excel 2016 (il faut quelques manipulations pour le réintégrer, voir ci-dessous).
Finalité
PowerView est un outil de reporting permettant de créer des tableaux de bord en utilisant directement des données d’Excel ou de PowerPivot. C’est un outil visuel dans lequel il suffit de faire des glisser / déposer pour créer des graphiques de toutes sortes. En résumé, Power View est un outil permettant d’explorer, de visualiser et de présenter vos données de façon interactive. J’avoue que je ne l’exploite pas car je préfère, de manière générale utiliser les fonctions d’Excel (Tableaux Croisés Dynamiques, Graphiques, …).
Comment l’activer avec Excel 2016 ?
Pour l’activer, voici la manipulation à réaliser, étape par étape:
- Ruban: Fichier > Options ;
- Personnaliser le ruban ;
- Sur la partie droite (Personnaliser le ruban), Positionnez-vous sur l’onglet Insertion ;
- Cliquez sur le bouton « Nouveau groupe » et nommez le comme vous le souhaitez (ex: Power view) ;
- Sur la partie centrale, dans la liste déroulante « Choisir les commandes dans les catégories suivantes« , sélectionnez « Toutes les commandes » ;
- Cherchez ensuite « PowerView« , sélectionnez-le et cliquez sur « Ajouter » ;
- Cliquez sur OK et vous devriez avoir PowerView ajouté à l’onglet « Insertion« .
Exemple
Voici un exemple de ce que nous pouvons faire en deux clics d’après les données issues de PowerPivot.
Lancement de Power View:
Exemple de graphique fait avec Power View:
PowerMap ou Carte 3D
Historique
Power Map a démarré avec la version Excel 2013 puis a changé de nom (avec quelques changements sur les fonctionnalités) en passant sur Excel 2016. Il s’appelle désormais « Carte 3D » et on le trouve sur l’onglet « Insertion« .
Finalité
Carte 3D permet des créer des cartes interactives sur lesquelles peuvent figurer des données à la fois liées à l’espace (Adresses, Villes, Pays, Continents, …) et au temps. On pourra donc parler de données spatio-temporelles ! En effet, il est par exemple possible de voir évoluer dans le temps les ventes quartier par quartier et ainsi créer des petites vidéos.
Pour utiliser cet outil, il faudra obligatoirement être connecté à Internet.
Exemple
Nous continuons notre exemple issu de PowerQuery et PowerPivot pour réaliser une petite vidéo grâce à Power Query. Ici il s’agit d’une image Gif animée.
Lancement de Carte 3D:
Exemple de carte réalisée dans Carte 3D:
Exemple de vidéo (Progression Chiffre d’affaire par ville dans le temps):
Comment s’interfacent ces outils ?
Avec PowerQuery, vous pouvez extraire de très nombreux types de données issues de sources très variées. Vous pouvez également importer dans PowerPivot mais la variétés de types de sources est plus restreint. Ensuite avec PowerQuery, vous pouvez transformer vos données importées. Ainsi à chaque fois que vous actualiserez vos données, les transformations seront réalisées automatiquement. Pour Actualiser, il suffit d’aller dans le Ruban: Données > Connexions > Actualiser Tout.
Une fois vos données prêtes, vous pouvez les charger soit dans Excel, soit dans PowerPivot. Dans Excel, un tableau sera automatiquement créé, tandis que dans PowerPivot, vous pourrez voir les données uniquement si vous cliquez sur « Gérer le modèle de données » (Ruban: Données > Outils de données > Gérer le modèle de données) ou sur Gérer dans l’onglet PowerPivot du ruban.
Dans PowerPivot, vous allez pouvoir créer les relations ente les diverses données et ainsi créer des modèles dits en « étoile » ou en « flocon » puis ajouter des colonnes calculées, des mesures (cellules calculées), des KPI (Indicateurs).
Enfin vous pourrez vous appuyer sur ce modèle de données pour créer des tableaux de bord grâce à PowerView, Carte 3D ou les classiques Tableaux Croisés Dynamiques (TCD) et graphiques d’Excel.