Contact: 514-605-7112 / info@lecfomasque.com

PowerQuery: Importer, transformer et fusionner des tables de données dans Excel

Publié le : 1 avril 2014

Excel 2013 offre de nombreuses possibilités pour construire des bases de données plus aisément. L’une d’entre elles est le Add-in PowerQuery, qui permet notamment d’importer, de transformer et de fusionner des tables de données. Cet add-in existe aussi pour Excel 2010 mais il me semble plus performant en 2013.

.

Installation de PowerQuery

Notez que vous devez d’abord vous rendre dans les options Excel (menu Fichier/File), choisir l’option Add-ins et activer PowerQuery, dans les COM Add-ins. Par la suite, vous verrez apparaître le menu PowerQuery, dans votre barre de menus.

.

Importation de tables de données

PowerQuery vous permet d’importer des tables de données de diverses sources (Excel, Texte, CSV, My SQL, Web, Oracle, IBMDB2 etc.). Dans l’exemple ci-bas, j’importe simplement deux tables de données Excel.

PowerQuery Append

.

Modification de la table1

Une fois la table de données sélectionnée et importée via PowerQuery, il est possible de lui apporter des modifications, comme par exemple, supprimer des colonnes, insérer de nouvelles colonnes avec des calculs personnalisés, etc.

PowerQuery Append

.

Il est également possible d’appliquer des filtres numériques sur les champs numériques (ne conserver que les valeurs supérieures à la moyenne, ne présenter que le top 10 des valeurs, etc.) et d’appliquer des filtres textuels sur les champs textuels (ne conserver que les données qui commencent par ou qui contiennent l’expression suivante, etc.).

PowerQuery Append

.

Une fois les étapes de transformation terminées, la table1 est importée dans une feuille Excel. Par la suite, il sera toujours possible d’appuyer sur un bouton « rafraîchir/refresh » pour importer les nouvelles données de la table et ainsi la mettre à jour. Il sera aussi possible de changer les étapes de transformation des données au fil du processus d’importation au besoin. Un menu rattaché à la table1 apparaît lorsque l’on clique dans la table (voir image ci-bas).

PowerQuery Append
PowerQuery Append

.

Importation de la table2

Il est possible d’importer autant de tables de données que souhaité, en procédant toujours de la même façon.

.

Fusionner deux tables

PowerQuery permet de fusionner des tables de données de deux façons différentes (Append (ajout vers le bas/ajout de lignes) et Merge (ajout vers la droite/ajout de colonnes).

.

Append (Ajout)

Pour ajouter deux tables, bout à bout, vous devez choisir l’option Append (ajout d’une table à la suite d’une autre table). Lors du processus d’ajout, vous aurez également la possibilité de transformer les tables fusionnées (ajouter des colonnes de calculs, retirer des colonnes ou des lignes, supprimer les doublons, etc.).

PowerQuery Append
PowerQuery Append
PowerQuery Append

.

Ajout de la table2 à la suite de la table1

Précisions d’abord que la table1 et la table2, dans notre exemple, sont deux tables identiques. Une fois que l’on applique la procédure décrite ci-haut, les deux tables sont fusionnées et apparaissent donc dans une seule table, une à la suite de l’autre (ajout de lignes).

PowerQuery Append

.

Analyse des données fusionnées

Une fois l’opération de fusion effectuée, il est possible d’analyser les nouvelles données fusionnées à l’aide notamment d’un tableau croisé dynamique et de segments (slicers).

PowerQuery Append

.

Fusion de la table2 et la table3

Il est également possible de fusionner deux tables de données qui comportent des champs communs. Par exemple, une table avec les revenus par client par région (table2) et une table avec les coûts par client par région (table3).

Power Query Merge
Power Query Merge

.

Table fusionnée

Une fois ce type de fusion effectuée, le résultat est une table comportant les coûts et les revenus par client par région, donc un ajout de colonnes (voir image ci-bas).

Power Query Merge

.

Conclusion

PowerQuery permet donc d’importer des tables de données de différentes sources, de transformer les données au passsage et de les fusionner entre elles de diverses façons. Autrement dit, PowerQuery peut servir de ETL (extracting, transforming & loading data) et il est un outil excessivement intéressant. Pour connaître les limites de puissande de l’option « Data Model », rendez-vous sur le site de Microsoft.

 


NOTRE CATALOGUE DE FORMATIONS


Catalogue de formations

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Pour info: 514-605-7112 ou info@lecfomasque.com

Téléchargez notre catalogue

Sophie Marchand, M.Sc., CPA, CGA, MVP

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA et d’un titre MVP (Most valuable professional) Excel de Microsoft, et cumule plus de 12 années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

  • répondre PowerQuery: Importer, transformer et fusionner ... ,

    […] Excel 2013 offre de nombreuses possibilités pour construire des bases de données plus aisément. L'une d'entre elles est le add-in PowerQuery, qui permet no  […]

    • répondre maurice ,

      Bonjour Sophie.

      Qelles sont les principales différences entre power pivot et power querry s’il vous plaît ?

      Cordialement
      Un fidèle lecteur de votre excellent blog

      • répondre Jean-Pierre Girardot ,

        Bonjour Sophie,
        très bon article, juste une petite remarque concernant le nombre de lignes, si on coche Load to Data Model , alors la limite du nombre de lignes est celle du data Model de PowerPivot et on peut gérer plusieurs millions de lignes.

        • répondre smarchand ,

          Bonjour Jean-Pierre,

          Merci pour l’info. Je l’ai changée et j’ai redirigé les gens vers une page de Microsoft qui parle des limites de puissance de Data Model.

          Au plaisir,

          Sophie

        Écrire un commentaire


        • *