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

À propos de l’auteure

Sophie Marchand, M.Sc., CPA, CGA

Auteure du blogue Mon Cher Watson et du site le CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA 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 consultanteformatrice et conférencière.

smarchand@lecfomasque.com
514-605-7112

.
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.

 

Formations ExcelCalendrier de formations

Pour en savoir davantage sur nos formations ou pour vous inscrire, vous pouvez consulter notre calendrier de formation.

Sophie Marchand, M.Sc., CPA, CGA, MVP
514-605-7112
Experte en modélisation financière et intelligence d’affaires

 

Vous avez des questions?

Posez toutes vos questions sur le forum du CFO masqué.

Tagués avec : , , ,
Publié dans Excel, Tableaux de bord, Tous les articles
3 commentaires pour “PowerQuery: Importer, transformer et fusionner des tables de données dans Excel
  1. maurice dit :

    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

  2. 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.

    • smarchand dit :

      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

1 Pings/Trackbacks pour "PowerQuery: Importer, transformer et fusionner des tables de données dans Excel"
  1. […] 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  […]

Laisser un commentaire

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

*


*

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Recevoir les articles par courriel
Abonnement Le CFO masqué
Gagnez une formation en ligne
Concours
Tableau de bord gratuit à l’inscription
Tableau de bord gratuit à l’inscription
Présentation à télécharger
Outils indispensables aux CFOs
Boutique en ligne
Microsoft Most Valuable Professional
Formations Excel
Formations Excel
Activités à venir
  1. Excel – Tableaux de bord (niveau 1)

    septembre 23 @ 9 h 00 min - 16 h 00 min
  2. Excel – Modélisation financière (niveau 1)

    septembre 30 @ 9 h 00 min - 16 h 00 min