Les Outils Power BI d’Excel


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

Outils BI_01

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

Web vers Excel

Voir article pour importer les cours de devises grâce à PowerQuery.

Database vers Cube

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:

Outils BI_05_Query

Requêtes à transformer:

Outils BI_07_Query

Charger dans un modèle de données:

Outils BI_08_Query

Chargement des requêtes:

Outils BI_09_Query

PowerPivot

Outils BI_02

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):

Outils BI_10_Pivot

Copie d’écran de données dans PowerPivot:

Outils BI_11_Pivot

Diagramme des relations dans PowerPivot:

Outils BI_12_Pivot

 

PowerView

Outils BI_03

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:

  1. Ruban: Fichier > Options ;
  2. Personnaliser le ruban ;
  3. Sur la partie droite (Personnaliser le ruban), Positionnez-vous sur l’onglet Insertion ;
  4. Cliquez sur le bouton « Nouveau groupe » et nommez le comme vous le souhaitez (ex: Power view) ;
  5. Sur la partie centrale, dans la liste déroulante « Choisir les commandes dans les catégories suivantes« , sélectionnez « Toutes les commandes » ;
  6. Cherchez ensuite « PowerView« , sélectionnez-le et cliquez sur « Ajouter » ;
  7. 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:

Outils BI_13_View

Exemple de graphique fait avec Power View:

Outils BI_14_View

 

PowerMap ou Carte 3D

Outils BI_04

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:

Outils BI_15_carte3D

Exemple de carte réalisée dans Carte 3D:

Outils BI_16_carte3D

Exemple de vidéo (Progression Chiffre d’affaire par ville dans le temps):

Outils BI_17_carte3D

 

Comment s’interfacent ces outils ?

Schéma

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.

Laissez un commentaire

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