- Power Query vous permet de consolider plusieurs fichiers provenant d'un dossier local ou cloud, à condition qu'ils partagent le même schéma de données.
- L'utilisation d'un fichier d'exemple facilite l'application de transformations en masse, telles que la suppression des premières lignes ou la modification des en-têtes.
- Des limitations surviennent lorsque les colonnes ne correspondent pas exactement, ce qui nécessite un nettoyage manuel ou l'utilisation de scripts VBA pour les cas complexes.
Imaginez que vous ayez une multitude de rapports mensuels ou hebdomadaires éparpillés dans différents dossiers. Au lieu de perdre votre matinée à copier-coller frénétiquement des données, vous pourriez tout regrouper dans une seule feuille de calcul Excel en quelques clics. C'est précisément là qu'intervient Power Query, un outil indispensable pour quiconque travaille avec des données, permettant de consolider sans effort des fichiers ayant la même structure.
Ce qui est formidable avec ce système, c'est qu'il ne se limite pas à un dossier local sur votre PC ; vous pouvez extraire des données de sites SharePoint, d'Azure Data Lake ou d'Azure Blobs . Pour un fonctionnement optimal, il est essentiel que les fichiers aient le même schéma, c'est-à-dire que les colonnes correspondent. Power Query est suffisamment intelligent pour nettoyer les données si elles contiennent des informations parasites ou des lignes inutiles.
Le processus de combinaison étape par étape
Lorsque vous vous connectez à une source de données, la première chose qui s'affiche est l'interface du système de fichiers. La colonne la plus importante est « Contenu », qui contient la représentation binaire de chaque document. En cliquant sur l'une de ces valeurs binaires, le programme tentera de déterminer s'il s'agit d'un fichier CSV, JSON ou Excel afin de l'ouvrir. Pour fusionner l'ensemble des fichiers, deux options s'offrent à vous : cliquer directement sur le bouton « Fusionner » ou, pour une sélection plus précise, sélectionner « Transformer les données » afin de filtrer la liste des fichiers (par exemple, pour ignorer certains dossiers) avant de procéder à la fusion.
Une fois la boîte de dialogue de fusion ouverte, Power Query utilise un fichier d'exemple pour analyser la structure . Par défaut, il sélectionne le premier fichier de la liste, mais vous pouvez le modifier si un autre fichier représente mieux l'ensemble de données. Vous pouvez également sélectionner l'option permettant d' ignorer les fichiers contenant des erreurs ; cette fonction est essentielle lorsqu'un rapport est corrompu ou vide, car elle évite l'arrêt complet du processus.
Gestion des données de sortie et nettoyage des données
Une fois la fusion acceptée, le système effectue des opérations techniques en arrière-plan : il crée une requête d'exemple et une fonction paramétrée. Ainsi, toute modification apportée au fichier d'exemple sera automatiquement répercutée sur tous les autres fichiers du dossier. Par exemple, si vos fichiers CSV contiennent quatre lignes d'en-tête inutiles avant les données, vous pouvez accéder à la requête de transformation et utiliser l'option permettant de supprimer les premières lignes pour simplifier le traitement.
Après avoir nettoyé les lignes, il est essentiel d'appliquer la fonction « Utiliser la première ligne comme en-têtes » . Notez que Power Query ajoute généralement une étape de conversion automatique des types de données. Si, en revenant à la requête principale, vous voyez une erreur indiquant que la colonne « Column1 » est introuvable, ne vous inquiétez pas : c'est normal, car vous avez renommé les colonnes dans l'exemple et l'étape suivante ne reconnaît plus les anciens noms. Supprimez simplement cette étape erronée du volet Étapes appliquées, et tout rentrera dans l'ordre.
Défis communs et alternatives avancées
Parfois, la vie n'est pas si simple et nous rencontrons des fichiers non identiques. Par exemple, si vos rapports ServiceNow contiennent un en-tête nommé « resolved_on » et un autre « closed_on », Power Query peut poser problème car il exige une structure cohérente . Si le fichier d'exemple ne correspond pas exactement aux autres, l'outil risque d'omettre les informations des fichiers non concordants ou de générer des erreurs d'importation.
Lorsque la structure est totalement chaotique ou que le volume de données est massif (plus d'un million de lignes Excel), d'autres solutions existent. Certains utilisateurs optent pour des scripts VBA utilisant des interfaces CSV , permettant de fusionner des fichiers sans répétition d'en-têtes, même si le délimiteur de champ ou d'enregistrement varie d'un fichier à l'autre. Si l'interpréteur de commandes Windows est extrêmement rapide, il rencontre souvent des difficultés avec la gestion des sauts de ligne, tandis qu'une solution VBA robuste n'encombre pas la mémoire vive et peut traiter des millions d'enregistrements sans problème.
Pour finaliser le processus, une fois les données combinées dans Power Query, n'oubliez pas d'attribuer le type de données approprié à chaque colonne (Date pour les dates, Devise pour les revenus, etc.) avant de charger le tableau final. Vous pouvez vérifier que tout est correct en filtrant la colonne Nom de la source afin de vous assurer que tous les fichiers du dossier ont été traités avec succès.
La possibilité d'automatiser la fusion de plusieurs fichiers CSV vous permet de passer d'un travail manuel fastidieux à un flux de travail professionnel, que ce soit en tirant parti de la flexibilité de Power Query pour le nettoyage des données ou en recourant à VBA lorsque les structures sont incompatibles ou que les volumes dépassent les limites des feuilles de calcul.