Fusionner tous les fichiers d’un dossier

C’est la rentrée ! Du coup, j’ai décidé de vous proposer un petit exercice pratique qui utilise PowerQuery (Récupérer et Transformer dans Excel 2016) de façon très simple. Cet article est directement dédié aux personnes qui ne se servent pas encore vraiment de PowerQuery et qui veulent découvrir toute la puissance de cet outil. L’idée de l’exercice est très simple et je pense que vous avez déjà eu ce cas là.

Présentation du cas

Dans un dossier, j’ai les Chiffres de chaque mois dans un fichier différent. Les fichiers sont au format .CSV, format que nous retrouvons beaucoup comme le résultat d’une extraction de données. Ce que j’aimerais faire est regrouper toutes les données dans un seul fichier (plus pratique pour faire un tableau de bord par exemple). Mais surtout j’aimerais ne pas refaire cela chaque mois qui passe. En résumé, si je pouvais ajouter des nouveaux fichiers dans le dossier, j’aimerais que mon fichier global se mette à jour en seulement 1 ou 2 clics maxi. Et comme je n’y connais rien en programmation et en VBA (enfin si un peu quand même…, c’est juste pour l’exemple !), j’aimerais éviter de taper la moindre ligne de code.

Cela est très facile avec PowerQuery et je vais vous montrer comment faire.

Télécharger les fichiers

Il s’agit d’un fichier ZIP qu’il vous faut dézipper. Vous y trouverez 2 dossiers:

  • CA 2015 : Contient les fichiers de Janvier 2015 à Juin 2015
  • CA 2015 S2: Contient les fichiers de Juillet 2015 à Décembre 2015

Notre objectif sera donc d’importer tous les fichiers contenus dans le dossier CA 2015. Dans un premier temps, nous allons donc ajouter tous les fichiers de janvier à juin.

 

Fusionner tous les fichiers d’un dossier

Extraire

La première étape est donc l’Extraction des données. En somme, il va falloir définir le dossier où se trouvent les fichiers et en extraire les données. Créez un nouveau classeur Excel vide et sauvegardez-le.

Ruban: Données > Récupérer et transformer > A partir d’un fichier > A partir d’un dossier.

Fusionner 01

Une nouvelle fenêtre apparaît vous demandant de choisir le dossier où se trouvent les fichiers. Choisissez CA 2015, le dossier que vous avez téléchargé plut tôt. L’écran suivant apparaît, cliquez simplement sur Modifier.

Fusionner 02

La fenêtre PowerQuery s’ouvre et vous pouvez voir une nouvelle table contenant la liste des fichiers du dossier. Dans cet article, nous allons rester sur des actions simples. Par exemple, nous n’allons pas faire de contrôle sur le nom des fichiers permettant ainsi de ne garder que les fichiers souhaités. L’objectif étant de vous montrer comment on peut regrouper des fichiers très simplement.

Cliquer sur le bouton contenant les deux flèches, comme ci-dessous:

Fusionner 03

 

Transformer

Ainsi, vous pourrez voir l’ensemble du contenu des fichiers dans le tableau qui suit. Après l’extraction, nous pouvons donc passer à l’étape suivante: La transformation, qui consiste en la mise à jour des données extraites. A présent, observez le tableau. Vous remarquerez deux points particuliers:

  1. Le titre des colonnes n’est pas le bon et il apparaît en première ligne. D’ailleurs, nous pouvons donc nous douter que les titres apparaîtront plusieurs fois dans la table, 1 fois pour chaque fichier du dossier. Nous devrons donc tout d’abord utiliser la première ligne comme titre puis éliminer les autres lignes de titre.
  2. Les étapes appliquées sur la droite indiquent que le type de certaines colonnes a été modifié. Il est important que chaque colonne ait le bon type mais nous le ferons à la fin. Du coup, vous pouvez supprimer cette étape en cliquant que la petite croix à gauche de celle-ci.

Fusionner 04

Voici donc comment utiliser la première ligne comme titre de colonne:

Ruban: Transformer > Table > Utiliser le première ligne pour les en-têtes.

Fusionner 05

Les titres des colonnes ont bien été mis à jour. Comme je le disais plus haut, les titres s’affichent au milieu du tableau, à chaque fois que nous avons extrait un fichier du dossier. Il faut donc supprimer ces lignes.

Fusionner 06

L’astuce est la suivante:

Avec PowerQuery, il est possible de supprimer les lignes lorsqu’une erreur est trouvée. Il faudrait donc arriver à mettre en erreur ces lignes (de titre) sans mettre les autres en erreur. Il s’agit d’un cas très classique dans l’utilisation de PowerQuery car nous sommes confrontés très souvent à ce type de problème. Nous avons une colonne nommée “Date” qui contient des valeurs correspondant à des dates sauf pour les lignes ayant le titre, où nous avons la valeur “Date”. L’astuce est de modifier le type de données en le passant au type “Date”. Toutes les cellules passeront au type Date, sauf celles qui contiennent le titre car PowerQuery ne peut pas convertir la valeur “Date” en date. Une erreur va donc apparaître.

Sélectionnez la colonne Date

Ruban: Accueil > Transformer > Type de données > Date.

Fusionner 07

Vous pouvez voir que les dates sont à présent alignées à droite et le mot erreur apparaît à la place du mot “Date”.

Fusionner 08

Il suffit à présent de supprimer les lignes contenant une erreur dans la colonne “Date”.

Sélectionnez la colonne “Date”

Ruban: Accueil > Supprimer les lignes > Supprimer les erreurs

Fusionner 09

Il ne reste plus qu’à mettre à jour le type pour chaque colonne et ensuite nous aurons presque terminé.

Sélectionner chaque colonne (vous pouvez sélectionner les 4 premières d’abord puis les autres ensuite)

Ruban: Accueil > Transformer > Type de données > Choisir le type souhaité

Colonnes Produit à Ville: Texte

Colonne Nb jours vendus: Nombre entier

Colonnes CA et %Remise: Nombre décimal

La colonne Date a déjà été mise à jour plus tôt.

Fusionner 10

 

Charger

Toutes les données sont donc extraites comme nous le souhaitions. Il suffit à présent de charger les données dans le fichier Excel et cela peut être fait en un seul clic !

Ruban: Accueil > Fermer > Fermer et charger

Fusionner 11

Le tableau est gris le temps du chargement et devient vert une fois chargé. Vous pouvez voir que nous avons 503 lignes chargées.

Fusionner 12

L’objectif étant de pouvoir réaliser des analyses sur l’ensemble des données de chaque fichier, nous allons créer un tableau croisé dynamique afin de vérifier que nous avons bien l’ensemble des informations. Vous pouvez le créer comme ci-dessous:

Fusionner 13

Ajouter d’autres fichiers

Mais toute la puissance de PowerQuery est de faire en sorte que nous ne recommencions pas les mêmes tâches encore et encore. Ajoutez des fichiers du dossier CA 2015 S2 dans le dossier CA 2015. Ensuite dans votre fichier Excel:

Ruban: Données > Connexions > Actualiser Tout

Fusionner 14

Il faut le refaire une seconde fois pour actualiser le tableau croisé dynamique pour voir le résultat.

Fusionner 15

Il existe bien une solution pour éviter de faire deux fois la même action (Actualiser les données): Il faut passer par un modèle de données et PowerPivot lors de la création (juste deux clics supplémentaires suffisent). Pour cela, faites un clic-droit sur la requête (à droite de l’écran), puis Charger dans. Cochez “Ajouter au modèle de données”. Ensuite, au lieu de créer le tableau croisé dynamique depuis la table Excel, il faut ouvrir PowerPivot et créer le tableau croisé dynamique depuis PowerPivot directement.

 

Conclusion

PowerQuery est vraiment simple. Auparavant, pour arriver à ce même exploit, il fallait programmer en VBA pour manipuler des fichiers. A présent, nous pouvons le faire en restant dans Excel et en seulement quelques clics. Et ceci n’est qu’un échantillon de ce qu’on peut faire avec PowerQuery. En sachant cela, vous n’aurez plus le même regard sur vos données lorsque vous utiliserez Excel.

Le nombre d’actions est réduit et l’actualisation en seulement deux clics. Et nullement besoin d’être un expert, PowerQuery s’apprend très facilement. En une journée, on peut acquérir tout ce qui est nécessaire.

Petit rappel des actions:

  • Choisir le dossier ;
  • Extraire le contenu ;
  • Mettre la première ligne en en-êtres de colonnes ;
  • Supprimer les titres au milieu du tableau (Passer au Type Date puis Supprimer erreurs) ;
  • Modifier le type de chaque colonne ;
  • Charger les données.

Laisser un commentaire

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