Power Query : l’outil incontournable dans Excel

Excel Power Query

Excel demeure encore et toujours l’unique et le meilleur outil de traitement de données pour les utilisateurs finaux. Mais il n’est souvent pas possible d’utiliser directement les listes fournies.

Il faut souvent encore arranger la mise en pages, songez aux opérations suivantes :

  • Supprimer les espaces superflues
  • Tout convertir en majuscules ou minuscules
  • Ajouter/supprimer des colonnes
  • Scinder des colonnes

L’on utilise généralement les options d’Excel proposées par défaut, mais Microsoft dispose depuis Excel 2013 d’une série d’outils efficaces qui permettent d’étendre considérablement la fonctionnalité d’Excel. Les principaux outils sont : Power Pivot, pour créer des modèles de données que vous pouvez ensuite visualiser plus efficacement avec Power View (le précurseur de Power BI pour créer un tableau de bord interactif) et Power Map (représentations géographiques). Un outil supplémentaire, très puissant et que vous pouvez maîtriser très rapidement, est également disponible : Power Query.

Ce post reprend les captures d’écran de Power Query pour Excel 2016, mais les fonctionnalités sont présentes également dans les versions précédentes d’Excel. Si vous possédez Excel 2016 (ou Office 365), vous pouvez utiliser Power Query immédiatement car cet outil est intégré par défaut dans Excel. Si vous utilisez encore Excel 2013, vous devez commencer par installer l’Add-In. Tenez cependant compte du fait que Power Query est développé de manière continue par Microsoft et que les captures d’écran reproduites dans ce post peuvent donc entre-temps être obsolètes.

 

Structure en un tour de main

Dans ce post, je m’appuierai sur un problème (simplifié) rencontré chez un client. Les données sont fournies au client de la manière illustrée dans le tableau ci-dessous. Ce tableau se compose de 2 colonnes :

  • Personne (= Persoon) : le nom du collaborateur qui avait suivi une ou plusieurs formations
  • Formations (= Cursussen) : une liste des formations que ce collaborateur avait suivies.

structuur in een handomdraai

 

Mais l’on s’intéressait principalement aux aspects suivants :

  • Combien de formations chaque personne a-t-elle suivies ?
  • Quel est le degré de popularité des formations ?
  • Quel est le degré de popularité d’un programme déterminé ? (Excel, Access, Word…)

 

Toutes ces informations peuvent être déduites de ce tableau, mais il n’est en réalité pas possible de faire grand-chose compte tenu de la structure erronée. Entretemps, mon interlocuteur avait déjà utilisé l’option Text to Columns et était occupé à composer le code VBA depuis quelques jours afin de modifier la structure. Comme il était bloqué, il a sollicité mon aide. Il nous a fallu 5 minutes de travail, sans écrire 1 lettre code, grâce à Power Query.

 

Quelles sont les différentes étapes que vous devez effectuer ?

Cliquez sur le tableau et sélectionnez l’option de menu Data – From Table

data from table

 

Power Query s’ouvre et vous découvrez votre tableau dans la fenêtre Power Query.

power query venster

 

Cliquez à droite sur l’en-tête Formations (= Cursussen) et choisissez l’option Split Column – By Delimiter.

split column

 

Définissez le séparateur et cliquez sur OK. Il s’agit dans ce cas-ci d’un point-virgule.

split column by delimiter

 

Les formations sont réparties par personne entre différentes colonnes.

gesplitste kolom

 

Cliquez à droite sur l’en-tête Personne (= Persoon) et sélectionnez l’option Unpivot Other Columns.

unpivot


Un beau tableau apparaît, affichant une personne et 1 formation par ligne.

gesorteerde lijst

 

Supprimez la colonne Attribute (cliquez à droite sur l’en-tête et sélectionnez l’option Remove).

Certaines valeurs sont précédées d’une espace. Pour supprimer cette espace, cliquez à droite sur l’en-tête Value et choisissez l’option Transform – Trim

trim

 

Vous obtenez le résultat suivant :

resultaat

 

À présent, nous devons encore extraire le programme, ce que nous pouvons faire en suivant la même procédure : cliquez à droite sur l’en-tête Value et sélectionnez à nouveau l’option Split Column – By Delimiter.

 

Étant donné que le programme et la rubrique sont systématiquement séparés par la combinaison ESPACE-TRAIT D’UNION-ESPACE, nous sélectionnons l’option Custom et encodons cette combinaison dans la zone de texte.

 

split by delimiter

 

Résultat :

resultaat

 

Pour renommer les colonnes, cliquez à droite sur l’en-tête et entrez le nom approprié.

hernoemen

 

Afin de pouvoir ensuite analyser les données, nous devons encore les charger dans Excel. Cliquez sur le bouton Close & Load dans le menu Home et vous vous retrouvez dans Excel avec les données correctement mises en forme.

inladen

 

La dernière étape consiste à analyser ces données à l’aide de fonctions, de graphiques ou de PivotTables (tableaux croisés dynamiques).

pivot tablesSource: www.xylos.com

Avatar photo

Auteur

Johan Vermeire est senior Learning Consultant chez Xylos. Il combine formations Excel et Access, et programmation VBA dans ces progiciels. Il analyse comme personne les besoins (de formation) des petites et grandes entreprises et apporte des solutions qui renforcent considérablement l’efficacité et la productivité de ces entreprises.

Lire aussi

Nieuws per domein

Les plus lus

Let's connect