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 OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

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.

 

Consulter la liste des formations offertes

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

 

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

        • répondre Rodolphe ,

          Bonjour Sophie,

          Tout d’abord, bravo pour toutes ces informations utiles disponibles sur Excel et en français !

          Je souhaiterais savoir si je suis sur la bonne piste avec PowerPivot et/ou PowerQuery.

          Actuellement j’utilise un tableau de bord réalisé en Excel mais qui va chercher des données stockées dans des tables externes récupérées avec du query à l’aide de VBA. N’étant pas un pro du VBA, je souhaiterais simplifier et moderniser cette façon de faire. La programmation avec un langage n’est pas accessible facilement aux néophytes comme moi.

          Ma question : est-il possible de récupérer de manière dynamique des données stockées dans des répertoires différents sachant que les noms des fichiers sont identiques mais pas les répertoires (dossiers) ?
          Pour schématiser, j’ai un fichier qui reprend la liste des clients (identifiant, nom, chemin de stockage des données).
          NRCLI CLIENT CHEMIN STOCKAGE
          00001 CLIENT1 C:\Données\client1
          00002 CLIENT2 C:\Données\client2

          Pour chaque client, les noms des fichiers sont strictement les mêmes.
          C:\Données\client1\fichier1
          C:\Données\client1\fichier2
          C:\Données\client1\fichier3

          C:\Données\client2\fichier1
          C:\Données\client2\fichier2
          C:\Données\client2\fichier3

          Avant de consacrer du temps à ce nouveau développement, je serais heureux de savoir si ce cas de figure est possible sans VBA pour éviter d’importer un tas de tables.

          Très cordialement.

          Un nouvel adepte du blog.

          • répondre Sophie Marchand, M.Sc., CPA, CGA, MVP ,

            Bonjour Rodolphe,

            Je compte rédiger un billet de blogue dans les prochains jours sur le sujet. Mais oui, ça se fait et ça se fait bien. Il faut manipuler un peu le code M pour faire fonctionner le tout mais c’est somme toute assez simple.

            Au plaisir,

            Sophie

          • répondre Rodolphe ,

            Super Sophie,

            Dans l’attente de ce billet afin de pouvoir avancer sur ce nouveau terrain en ce qui me concerne.

            Excellemment vôtre.

            Rodolphe

          • répondre Rodolphe ,

            Bonjour Sophie,
            Je poursuis mes investigations sur Power Query et me heurte à un problème de taille.
            Par VBA ou une importation Microsoft Query, j’arrivais à extraire les données de données stockées dans des fichiers dbf (structure Dbase). C’est sans doute la préhistoire, mais le souci est que les tables sont alimentées par un logiciel externe et que ces fichiers ont ce format.
            Vu le nombre de formats disponibles en power query, y-a-t-il un astuce pour accéder à ces fichiers avec Power Query ?
            Bàv

            Rodolphe

          Écrire un commentaire


          • *