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

Publié le 01 avril 2014
par Sophie Marchand M.Sc.
PowerQuery Append

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 Power Query, 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. Cet article vise donc à vous expliquer comment procéder à la fusion de tables dans Power Query.

 

Installation de Power Query

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

 

Importation de tables de données

Avant de procéder à une fusion de tables dans Power Query, il faut d’abord importer ces tables. Mentionnons que Power Query 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 Power Query, 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.

 

Fusion de tables dans Power Query

Power Query 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).

.

Fusion de tables dans Power Query via l’option 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

 

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

 

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

 

Fusion de table dans Power Query

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 sur la fusion de table dans Power Query

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

 


 

Formation complémentaire

Pour une introduction au langage M, suivez la formation Excel – Introduction à Power Query et au langage M.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Voici quelques commentaires d’apprenants ayant suivi la formation - Excel Introduction à Power Query et au langage M

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

15 réflexions sur “Power Query : Importer, transformer et fusionner des tables de données dans Excel”

  1. Ping : PowerQuery: Importer, transformer et fusionner ...

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

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

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

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

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

  6. Bonjour Sophie,

    Je vais examiner la video et livrer mes impressions.

    Pour l’import des fichiers dbf, j’arrive à y accéder avec power pivot avec la chaîne de connexion suivante :
    Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=”DSN=dBASE Files;DBQ=C:\EVOLUTION\TEXTOGR;DefaultDir=C:\EVOLUTION\TEXTOGR;Driver={C:\WINDOWS\SYSTEM\odbcjt32.dll};DriverId=533;FIL=dBase III;MaxBufferSize=512;PageTimeout=5;Database=c:\evolution\TEXTOGR”;Initial Catalog=c:\evolution\TEXTOGR

    Avec MS-QUERY, j’utilisais effectivement les boîtes de dialogues via le menu Données/Autres sources/provenance ms query. J’avais déjà installé le driver Dbase comme source ODBC.

    Mais dans Power Query, dans le menu “A partir d’autres sources” , je ne vois pas la possibilité d’écrire une chaîne de connexion ?

  7. Bonjour Sophie,

    J’ai regardé attentivement la vidéo qui donne déjà pas mal d’infos sur les possibilités de Power Query. J’avoue trouver déjà le niveau élevé par rapport à mes possibilités.

    Pour revenir à mon cas, outre le problème de se connecter aux fichiers de données à structure dbf, comme évoqué plut haut, je souhaite rendre dynamique le choix du répertoire de données.
    En fait je dois me connecter à 2 ou 3 fichiers qui portent exactement les noms mais stockés dans des répertoire différents.
    L’idée que souhaiterait mettre en place est que power query ou power pivot aille rechercher dans un cellule excel le le chemin du dossier où se trouve les 2 ou 3 fichiers.
    Est-il possible en langage M de remplacer le code :
    Let
    Source = Excel.Workbook(File.Contents(“C:\répertoire1\fichier1”)

    Par
    Source = Excel.Workbook(File.Contents(“le chemin du fichier se trouve dans la cellule a1 du fichier excel courant”) ?

    En fait je voudrais variabiliser d’un part le chemin d’accès et d’autre part, des bornes de 2 dates (lignes comprises entre date début et date fin). Ceci afin de rendre dynamique et utilisable par différents dossiers des tableaux d’analyse sans devoir dupliquer les query (uniquement 3 variables qui changent).

    J’avais posté un billet sur le forum developpez mais sans succès jusqu’à présent :
    http://www.developpez.net/forums/d1488320/logiciels/microsoft-office/excel/commande-sql-query-powerpivot-syntaxe-vba/

    Joyeux Noël XXL

    Et je cherche toujours….

  8. Ping : Nettoyage | Pearltrees

  9. minh trung nguyen

    Bonjour, à chaque fois je modifie une requête dans Power Query, et j’actalise un tableau dynamique fait de power pivot, il affiche toujours un message d’erreur comme quoi une modification a été apporté et il faut déactiver et réactiver le chargement. Savez-vous comment on procède pour ne pas refaire les changements dans powerpivot mon tcd. Merci

    1. Bonjour,

      Je ne suis pas certaine de comprendre. Quel est le message précisément? Vous ne devriez pas avoir à refaire quoi que ce soit à part actualiser votre requête, à moins que vous ayez effectué des modifications dans Power Pivot qui auraient dû être faites dans Power Query. Mais si vous utilisez une version récente, Excel ne devrait pas vous laisser faire donc merci de préciser le message obtenue et la chaîne d’actions avant de l’obtenir.

      Au plaisir,

      Sophie

Laisser un commentaire

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

Retour en haut